Excel OfficeExcel provides a variety of Statistical Functions that helps in analyzing data within a worksheet
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 »
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 »
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 »
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 »
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 »
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 »
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 »
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 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 »
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 »