Statistical Archives

Jun22022 by excelhelp13No Comments

Subtotal All Function in Excel

SUBTOTAL function is use to get a subtotal in a list or from database. SUBTOTAL function can either include or exclude values in manually hidden rows. By default, SUBTOTAL excludes values in rows hidden by a filter. The SUBTOTAL function automatically ignores other SUBTOTAL formulas that exist in references to prevent double-counting. Syntax:-=SUBTOTAL (function_num, ref1, [ref2], ...) Arguments function_num - A number that specifies which function to use in calculating subtotals within a list. See table below for full list.ref1 - A named range or reference to subtotal.ref2 - [optional] A named range or reference to subtotal. There are 11 functions available, each with two options, as seen in the table below. When function_num is between 1-11, SUBTOTAL includes cells that have...

Dec192020 by excelhelp13No Comments

SUMPRODUCT In Excel

FORMULAS, Maths, Statistical

DEFINITION:- The Sumproduct Function Multiplies Ranges Or Arrays Together And Returns The Sum Of Products. The main purpose of Sumproduct is to multiply the array or range and then add those value and return the sum of the particulars. SYNTAX:- =SUMPRODUCT (array1, array2, ...) EXAMPLE:-Suppose You Have A Few Items In Stock As Per The Image Below. If You Want To Know How Much The Total Price According To The Quantity, You Should Use The Sumproduct Formula. Explanation: The Sumproduct Function Performs This Calculation: Quantity * Price (2 * 20000) + (3* 15000) + (5 * 12000) + (4 * 9000) + (5*500) = 183500 VALIDATION:- The Ranges Must Have The Same Dimensions Or Excel Will Display The #Value! Error. SINGLE ARRAY:- If You Supply A Single Range Exampl...

Dec192020 by excelhelp13No Comments

SUMIF And INDIRECT To Sum On Multiple Sheet

FORMULAS, Lookup & Reference, Maths, Statistical

SUMIF function adds all numeric numbers in a given range of cells based on one criteria. Suppose there is name in column A and amount in column B and in column A if a name is repeating more than 1 time and you want to add all the amount It have next to column then Sumif is a best formula to make your work easy. DEFINITON INDIRECT:- INDIRECT function is used in Excel to change over a content string into a legitimate range reference. For example: if you want to lookup what value is available in cell A2 then simply use =INDIRECT(“a2”). Indirect use text reference and actually convert it to reference. So here “a2” is converted to cell(a2). NOTE: - Make sure to change the reference according to your range (database). FORMULA IN EXAMPLE : =SUMIF(INDIRECT(D2&"!$...

Dec22020 by excelhelp13No Comments

Compare Time Using If Formula

Logical, Statistical

First Select Any Of The Cell For Output Result. Apply The Formula '=IF(TIME(11,17,30)=D2,"Yes","No") Make Sure That Time After 12:00 Hr Is 13:00 As By Default The Time Value Is In 24 Hrs Format. If You Change The Format To 12 Hrs Then It Fine to Have 01;00 Hrs. But Its Vary With Time Formats. As A Result You Can Choose The Output With If Formula. That's It: :)

Nov302020 by excelhelp13No Comments

Rank Function In Excel

Maths, Statistical

Open Excel File.Select The Cell Where you Want To The Result To Get Populated.Then Add This Formula In Particular Cell =RANK(10,A1:A10)There Is Third Optional Parameter (Ascending / Descending ) =RANK(10,A1:A10, 1 'For Ascending Order') or =RANK(10,A1:A10, 0 For Descending Order)Make Sure To Lock The Range By $ Sign.Descending Order Is By Default Set Optional Parameter.That's it :)

Apr132020 by excelhelp13No Comments

FIND SMALLEST Number With Position In Excel

Statistical

First Select The Cell Where You Want The Result.Then Apply The Formula And Select The Array / Range And Define The Position. Once Applied It Will Check In The Array And Give You The Result By Finding The Position Of The Smallest Number.That's It :)

Apr72020 by excelhelp13No Comments

PRINT TITLE On Every Page In Excel

FORMULAS, Page Layout, Statistical

How to use the already built in function in excel for printing the title on every page. Please follow the step mentioned below. GO to Page Layout.Then go to Print Tiles and click on it.Then you will see One Dialog Box Will Appear.In that dialog box go to the Repeat On Top Option.After that select the option showing Title range.Then simply click ok button located at bottom right in dialog box.That's it :)

Apr42020 by excelhelp13No Comments

DROP DOWN List In Excel

FORMULAS, Statistical, Text

For Creating a Drop Down List just follow the few step shown below. Select a cell where you want to have your drop down list.Go to Data TabGo to Data Validation and click on it and one dialog box will pop up.Select List from that dialog box and go down one step into source row in that dialog box.Now select the range for the data you want to create a drop down list. For Ex:- A2:A10Once Selected just hit ok button in the dialog box and your drop down list is prepared.For Example see the step shown in the image and a video provided below.

Apr32020 by excelhelp13No Comments

SUBTOTAL With COUNT

FORMULAS, Maths, Statistical

SUBTOTAL is a great function available in excel. It only look the visible data and ignore the hidden data. So when you want to apply a Count function for only visible data only, when you are finding the data by applying filter or you have some hidden rows. The Count function count the number of numeric data available in the selected range. Use the formula at the end of your database where you want to apply this both function as shown in image and video example..

Mar262020 by excelhelp13No Comments

COUNTIFS In Excel

FORMULAS, Statistical

COUNTIFS- is used to determine how many time the Same value is available in the particular selected database in sheet. It will give the result by counting that this Criteria or Value or Name is available this many time in this database. This comes in use when you want to check more then one criteria. EX: how many data are there those ID - NAME AND GENDER are Same Basically the Countifs is used when you want to count on the basis of more than one criteria. You can also find on the basis of different sheet reference simply you have to provide the reference same as you do in single sheet. Countifs is really a best function in excel. PARAMETER:- (Criteria_range_1, Criteria 1, Criteria_range_2, Criteria 2 and so on ........... FORMULA =COUNTIFS(B2:D9,"Ram") ...