Summing 3 Rows, then finding the Highest average

**DHHM** · 09-09-2018, 02:35 AM

I want to return the highest averages from a sum of a 3 row period, as below:

Data.PNG

Data2.PNG

Data3.PNG

Data4.PNG

Is it possible for an array formula to do the work of cells C,D,E with offset, index etc, showing the highest average?

**AliGW** · 09-09-2018, 02:39 AM

Attach a workbook - we can't manipulate a picture.

**DHHM** · 09-09-2018, 02:56 AM

Sorry about that, I can't seem to find where to attach the file?

**AliGW** · 09-09-2018, 02:57 AM

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

**DHHM** · 09-09-2018, 03:00 AM

Thanks for the info.

See attached.

**AliGW** · 09-09-2018, 03:10 AM

You can get rid of column C thus:

=SUM(B2:B4)/3

I am not sure what you want, though, when you say:

Is it possible for an array formula to do the work of cells C,D,E ...

What will this look like? Are you wanting to have the highest average marked with Y without seeing the highest average column? If so, why? And also if so, why not just hide the column?

Excel 2016 (Windows) 32 bit

	A	B	D
1	Series	Amount	Max Average
2	1	40
3	2	23
4	3	40	Y
5	4	50
6	5	65
7	6	30
8	7	10
9	8	20
10	9	23
11	10	40

Sheet: Sheet1

**DHHM** · 09-09-2018, 03:20 AM

Yes, I could hide the column. It was more wanting to know if this was possible in an array.

**AliGW** · 09-09-2018, 03:24 AM

If it is, I think it will be quite complex and possibly processor heavy, especially if your real dataset is large. As a theoretical question, it will be interesting to see if it can be done, but as a practical solution ...? Not so sure.

The difficulty will be combining a rolling average calculation with a fixed range maximum calculation.

Let's see if the array formula wizards here can crack it.

**tim201110** · 09-09-2018, 06:33 AM

as a way
UDF

Please Login or Register  to view this content.

**jason.b75** · 09-09-2018, 06:46 AM

Is this what you want?

=MAX(SUBTOTAL(1,OFFSET(B2,ROW(B2:B11)-ROW(B2),0,3,1)))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

**AliGW** · 09-09-2018, 08:28 AM

That seems to work as a normal formula, Jason, without array-entering.

I think what the OP wants is for the formula to determine whether the row its' on is the one returning the maximum, and if so, return "Y".

PS Needs to be this, as the last two rows' results are not quite right with the formula in post #10:

=MAX(SUBTOTAL(9,OFFSET(B2,ROW(B2:B11)-ROW(B2),0,3,1))/3)

**jason.b75** · 09-09-2018, 09:25 AM

At a glance, it looks like it works without array confirming, but it only gets the average of B2:B5, none of the other ranges are evaluated.

Originally Posted by AliGW

PS Needs to be this, as the last two rows' results are not quite right with the formula in post #10:

But are the results correct, Ali?

The OP has asked for average of 3, but the last 2 results are false because there are less than 3 values to average.

This one gives the right result

=IF(SUM(B2:B5)/3=MAX(SUBTOTAL(9,OFFSET($B$2,ROW($B$2:$B$11)-ROW($B$2),0,3,1))/3),"Y","")

and this one gives the correct result.

=IF(SUM(B2:B5)/3=MAX(SUBTOTAL(1,OFFSET($B$2,ROW($B$2:$B$11)-ROW($B$2),0,3,1))),"Y","")

Regardless of which, array confirmation is needed before filling down.

**AliGW** · 09-09-2018, 11:08 AM

I'm working to what the OP presented as the results he wants. Formulae from posts #10 and #11 below (neither array-entered):

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F
1	Series	Amount	Average of 3	Max Average	Ali	Jason
2	1	40	34.33333333		34.3333333	34.33333
3	2	23	37.66666667		37.6666667	37.66667
4	3	40	51.66666667	Y	51.6666667	51.66667
5	4	50	48.33333333		48.3333333	48.33333
6	5	65	35		35	35
7	6	30	20		20	20
8	7	10	17.66666667		17.6666667	17.66667
9	8	20	27.66666667		27.6666667	27.66667
10	9	23	21		21	31.5
11	10	40	13.33333333		13.3333333	40

Sheet: Sheet1

**jason.b75** · 09-09-2018, 11:46 AM

Originaly that was a single cell formula to give the max average without filling down. If you enter it and array confirm, this is what it gives you, 51.66666667

Now realising that they still want the Y flag column to highlight the row, I've modified the formula in post #12 to compare the average for each group of 3 to the average for all groups of 3 and fag the row that contains the max average without the other columns.

Enter either formula form post 12, in D2 and fill down (array confirmed, you get Y in the right place, not array confirmed, you don't).

**DHHM** · 09-09-2018, 07:20 PM

Thanks Ali and Jason. I'd never thought out using Subtotal.

I've modified the range B2:B5 to B2:B4 in Jasons formula which returns the same result I was after.

**DHHM** · 09-09-2018, 07:21 PM

Thanks Tim.

**AliGW** · 09-10-2018, 01:08 AM

I presume you used the SUBTOTAL 9 option?

Summing 3 Rows, then finding the Highest average

LinkBack

Thread Tools

Rate This Thread

Display