Average of the 10 highest values by month

[trizzo]

Hello,

This is my first post here.

I need to have the average of the 10 highest values for each month.
Let`s consider that Month 1=January and 2=February.
The month values are on columns A and the values are on column B.

I tried to use this formula: =IF(A2:A31=1,AVERAGE(LARGE(B2:B31,{1,2,3,4,5,6,7,8,9,10})))
But it is not working, looks like it is not recognizing when I am saying A2:A31=1.
I should get the result 125 for month=1 and for month=2 should be 1850.

I was unable to attach an example in excel format.

Can someone help me please?


Month Value
1 30
1 40
1 50
1 60
1 70
1 80
1 90
1 100
1 110
1 120
1 130
1 140
1 150
1 160
1 170
2 900
2 1000
2 1100
2 1200
2 1300
2 1400
2 1500
2 1600
2 1700
2 1800
2 1900
2 2000
2 2100
2 2200
2 2300


Thanks!

[daddylonglegs]

Hello trizzo, you need the condition inside the LARGE function, try like this

=AVERAGE(LARGE(IF(A2:A31=1,B2:B31),{1,2,3,4,5,6,7,8,9,10}))

confirmed with CTRL+SHIFT+ENTER

[trizzo]

Hey daddylonglegs it almost worked but it is not yet totally correct! Actually when I used A2:A31=1 to reflect the month=1 (January) I got the result from month=2 (February). The result was 1850.
I am not sure if this is the most useful formula in this case, maybe it is not. This is like a query, if month=1 (January) I need to have the average of the 10 highest values for that month, when month=2 (February) the average of 10 highest values for that month. Is there any other formula or another way to make this?

[daddylonglegs]

If you get that result that means you haven't entered the formula correctly.

Put the formula in a cell, press F2 key to select formula then hold down CTRL and SHIFT keys and press ENTER, if done correctly you'll see curly braces around the formula in the formula bar, so the formula looks like this (note: you can't put the curly braces in manually)

={AVERAGE(LARGE(IF(A2:A31=1,B2:B31),{1,2,3,4,5,6,7,8,9,10}))}

an alternative is to use this version which doesn't need that key combination

=AVERAGE(LARGE(INDEX((A2:A31=1)*B2:B31,0),{1,2,3,4,5,6,7,8,9,10}))

[trizzo]

I did exactly what you said and I got a message saying 'The formula you typed contains an error'. However I was able to use the alternative formula you pasted here:

=AVERAGE(LARGE(INDEX((A2:A31=1)*B2:B31,0),{1,2,3,4,5,6,7,8,9,10}))

This one worked great! Thanks a lot!! Could you please explain me this above formula? I did not understand you did an *...

Now let`s say if I want to add another two conditions in this same formula, how should I make it? Like this the data I have, let`s assume I have columns A to D, with these fours fields and their values:

Month Value Period System
1 30 NIGHT X
1 40 NIGHT X
1 50 NIGHT X
1 60 NIGHT X
1 70 NIGHT X
1 80 NIGHT X
1 90 NIGHT X
1 100 NIGHT X
1 110 NIGHT X
1 120 NIGHT X
1 130 NIGHT X
1 140 NIGHT X
1 150 NIGHT X
1 160 NIGHT X
1 170 NIGHT X
2 900 PRIME Y
2 1000 PRIME Y
2 1100 PRIME Y
2 1200 PRIME Y
2 1300 PRIME Y
2 1400 PRIME Y
2 1500 PRIME Y
2 1600 PRIME Y
2 1700 PRIME Y
2 1800 PRIME Y
2 1900 PRIME Y
2 2000 PRIME Y
2 2100 PRIME Y
2 2200 PRIME Y
2 2300 PRIME Y


Let`s say I want the average of the 10 highest values that the month=1, period=night and system=X.

Is this the best formula to use on this case or there is another one?

Thanks!!

[daddylonglegs]

My personal preference is the array formula. For your new question this formula should work

=AVERAGE(LARGE(IF((A$2:A$31=E2)*(C$2:C$31=F2)*(D$2:D$31=G2),B2:B31),{1,2,3,4,5,6,7,8,9,10}))

confirmed with CTRL+SHIFT+ENTER

where E2, F2 and G2 contain the 3 criteria

...but will give you an error if there aren't 10 rows which match the criteria

This version will work as a "non-array" again

=AVERAGE(LARGE(INDEX((A$2:A$31=E2)*(C$2:C$31=F2)*(D$2:D$31=G2)*B2:B31,0),{1,2,3,4,5,6,7,8,9,10}))

it returns a zero if there aren't enough rows matching the criteria.

The * gives you an "AND" effect....

See attached

[trizzo]

Thank you very much daddylonglegs!

[trizzo]

Now with the same formula AVERAGE LARGE IF, I am using Names. Let`s say I have 3 columns (1 for YEAR, 1 for PERIOD_NAME and 1 for MIPS).
Here are their respective OFFSETS I created in their Names:
YEAR_BAD3F: =OFFSET('BAD3F Data'!$A$2,,,COUNT('BAD3F Data'!$A$2:$A$1048576))
PERIOD_NAME_BAD3F: =OFFSET('BAD3F Data'!$B$2,,,COUNT('BAD3F Data'!$B$2:$B$1048576))
MIPS_BAD3F=OFFSET('BAD3F Data'!$C$2,,,COUNT('BAD3F Data'!$C$2:$C$1048576))

When using the below formula it is not working:
=AVERAGE(LARGE(IF((YEAR_BAD3F=A38)*(PERIOD_NAME_BAD3F=$A$36),MIPS_BAD3F),{1,2,3,4,5,6,7,8,9,10}))
A38 Value = 2011
$A$36 Value = P02


Let`s say this is the sheet I have:
YEAR PERIOD_NAME MIPS ---> This is the 1st row
2012 P02 74.15
2012 P02 100.15
2012 P02 110.00
2012 P02 74.15
2012 P02 700.15
2012 P02 610.00
2012 P02 174.15
2012 P02 300.15
2012 P02 410.00
2012 P02 300.22
2012 P02 300
2012 P02 402




Looks like the offset formula is not working when I have a column with text like the column PERIOD_NAME. When I am using only numbers it is working.

Can someone help please? Thanks!

[trizzo]

I think I found out why it is not working because text we use COUNTA instead of COUNT, looks like it worked with this:

=OFFSET('BAD3F Data'!$G$2,0,0,COUNTA('BAD3F Data'!$G$2:$G$1048576))

=AVERAGE(LARGE(IF((YEAR_BAD3F=A38)*(MONTH_BAD3F=B38)*(PERIOD_NAME_BAD3F=A36),MIPS_BAD3F),{1,2,3,4,5,6,7,8,9,10}))