Statistical Functions

Excel provides a variety of Statistical Functions that helps in analyzing data within a worksheet

Check multiple cells have same value in Excel

To confirm that a range of cells all have the same value, you can use a formula based on the COUNTIF function. See illustration below: Formula =COUNTIF(range,”<>value”)=0 Explanation  In the example shown, the formula in C9 is: =COUNTIF(C5:C8,”<>ok”)=0 Note: this formula is not case-sensitive, you can find a case-sensitive formula here. How this formula works This… read more »

How to get last row in mixed data with no blanks in Excel

To get the last relative position (i.e. last row, last column) for mixed data that contains no empty cells, you can use the COUNTA function. See example below: Formula =COUNTA(range) Explanation In the example shown, the formula in D5 is: =COUNTA(B4:B100) Last *relative* position When constructing more advanced formulas, it’s often necessary to figure out… read more »

How to test for all values in a range are at least in Excel

To test if all values in a range are at least a certain threshold value, you can use the COUNTIF function together with the NOT function. Formula =NOT(COUNTIF(range,”<65″)) Explanation In the example shown, the formula in G5 is: =NOT(COUNTIF(B5:F5,”<65″)) How this formula works At the core, this formula uses the COUNTIF function to count any… read more »

Excel Rank without ties Example

This tutorials shows how to Rank numbers without  ties  in Excel. To assign rank without ties, you can use a formula based on the RANK and COUNTIF functions. Formula =RANK(A1,range)+COUNTIF(exp_range,A1)-1 Explanation In the example shown, the formula in E5 is: =RANK(C5,points)+COUNTIF($C$5:C5,C5)-1 where “points” is the named range How this formula works This formula breaks ties with… read more »

Excel Rank with ordinal suffix Example

This tutorials shows how to Rank with ordinal suffix in Excel. To add an ordinal suffix to a number (i.e. 1st, 2nd, 3rd, etc.) you can use a formula based on the CHOOSE function to assign the suffix. Formula =CHOOSE(number,”st”,”nd”,”rd”,”th”,”th”,”th”,”th”,”th”,”th”,”th”) Explanation In the example shown, the formula in C5 is: =CHOOSE(B5,”st”,”nd”,”rd”,”th”,”th”,”th”,”th”,”th”,”th”,”th”) How this formula works… read more »

Excel Rank race results Example

This tutorials shows how to Rank race results in Excel. To rank a set of race times, where the lowest (fastest) time is ranked #1, you can use the RANK function. Formula =RANK(time,times,1) Explanation In the example shown, the formula in D6 is: =RANK(C6,times,1) Where times is the named range C6:C13. How this formula works You can use the… read more »

Excel Rank function Example

This tutorials shows how to rank a set of numeric values, where the highest value is ranked #1 in Excel. To achieve this you can use the RANK function. Formula =RANK(value,data) Explanation In the example shown, the formula in D6 is: =RANK(C6,scores) Where scores is the named range C6:C13. How this formula works You can use the… read more »

Excel Rank if formula Example

This tutorials shows how to rank items in a list using one or more criteria in Excel. To achieve this you can use the COUNTIFS function. Formula =COUNTIFS(criteria_range,criteria,values,”>”&value)+1 Explanation In the example shown, the formula in E5 is: =COUNTIFS(groups,C5,scores,”>”&D5)+1 where “groups” is the named range C5:C14, and “scores” is the named range D5:D14. The result is a… read more »

Excel Statistical Functions

Excel provides a variety of statistical functions that might be useful for you know in calculations. Here’s a full list of Built-In Statistical Functions in Excel and their Descriptions below: Count & Frequency COUNT Returns the number of numerical values in a supplied set of cells or values COUNTA Returns the number of non-blanks in a… read more »

Forecast vs Trend Function in Excel

The FORECAST and TREND function give the exact same result. When you add a trendline to an Excel chart, Excel can display the equation in a chart (see below). You can use this equation to calculate future sales. Explanation: Excel uses the method of least squares to find a line that best fits the points. The R-squared value equals 0.9295, which is a… read more »

Sidebar