Monday, 13 August 2018

Excel highest, lowest and mid-point functions with sub-totals

Magic from Excel Off The Grid (19 February 2018)
I wanted an Excel function that would give me a top, mid and bottom mark within Excel's subtotal function, so I could see the different stats for different groups. I also wanted the function to ignore hidden rows, and to ignore zeros. 

Many times I had ventured out into the Excel help files in the past decade, and each time I had been driven back. But recently and wonderfully, I ran across a very helpful blog which uses the Aggregate function to do exactly what I want (Excel Off The Grid, 19 February 2018).

After an hour or so's searching and trialling, I have now transformed my old "=Quartile(A3:A120, 2)" function into "=AGGREGATE(19,1,A3:A120,2)". This calculates my mid-quartile, ignores zero scores and hidden rows, and does a subtotal

What this breaks down to is: function 19 (ie, quartile excluding zeros); option 1 (ie, "Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions");  the range I want the function to calculate on (column A, rows 3 to 120); and quartile function "2", which is the mid-point.

The other two things I have used are SMALL and LARGE:
  • "=AGGREGATE(15,1,A3:A120,1)" which gives me the lowest number in my column; 
  • and =AGGREGATE(14,1,A3:A120,1), which gives me the highest number in my column. 
  • If I changed out the last "1" in each of these brackets for 5, say, I would get the 5th highest/lowest number.

So clever. The following table shows what each function number delivers within the Aggregate formula (Excel Off The Grid, 19 February 2018):



function_num Calculation performed Description Function Form
1 AVERAGE Returns the average (mean) Reference
2 COUNT Counts number of values or cells which contain numbers Reference
3 COUNTA Counts number of values or cells which are not empty Reference
4 MAX Returns the largest value Reference
5 MIN Returns the smallest value Reference
6 PRODUCT Multiplies all the numbers Reference
7 STDEV.S Estimates standard deviation based on a sample Reference
8 STDEV.P Calculates standard deviation based on a population Reference
9 SUM Adds all the values or cells in a range Reference
10 VAR.S Estimates the variance based on a sample Reference
11 VAR.P Calculates variance based on a population Reference
12 MEDIAN Returns the median (the number in the middle) Reference
13 MODE.SNGL Returns the most frequently occurring number Reference
14 LARGE Returns the nth largest value Array
15 SMALL Returns the nth smallest value Array
16 PERCENTILE.INC Returns the nth percentile of values in a range which includes the value Array
17 QUARTILE.INC Returns the quartile based on a percentile including the value Array
18 PERCENTILE.EXC Returns the nth percentile of values in a range which excludes the value Array
19 QUARTILE.EXC Returns the quartile based on a percentile excluding the value Array

Aggregate is a really cool Excel function which gives us stats like magic. I have already started using it wherever I can. 

Thanks, Excel Off the Grid!


Sam

No comments :

Post a Comment