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

• Reference: Excel Off The Grid (19 February 2018). AGGREGATE: The best Excel function you’re not using. Retrieved from https://exceloffthegrid.com/aggregate-the-best-excel-function-youre-not-using/