Helping a friend to: find average of last 5 golf outings?

[kydan54]

A friend of mine works at a Kentucky State Park golf store and apparently keeps a record of scores/dates of players that play rounds there. He needs a formula that would perform an average of the each persons last 5 last rounds (greater than 0) from a column on a spreadsheet. His spreadsheet must be based on dates played as some dates have a 0, hence the last 5 must be greater than . From searching the forum, there seems to be several functions that might apply but I'm just a novice. Any quick solutions? Thanks in advance for any assistance! KY Dan

[jason.b75]

To get the average of the last 5 > 0 from column B

=AVERAGEIFS(B2:B19,B2:B19,">0",$A$2:$A$19,">="&LARGE(IF(B2:B19>0,$A$2:$A$19),5))

Array confirmed with Shift Ctrl Enter.

I'm sure there must be a simpler formula but it is eluding me.

[mehmetcik]

Enter this formula into E2 using ctrl, shift and enter, then fill to G2 and then down to G6

Please Login or Register  to view this content.

Enter this formula into E7 and then fill to G7

Please Login or Register  to view this content.

[kydan54]

Hey mehmetcik! Thanks for responding so quick! Unfortunately, I must be doing something incorrect? The formulas in E2-G6 seem to pull the "Highest 5 rounds per person" vs the "5 scores >0 from the bottom"? Can you advise me as to what I might be doing incorrectly? I appreciate the help!

[kydan54]

Hey jason.b75! You're pretty quick too! I tried your formula but I get a #Div/0! error. Is there a particular cell location I need to enter the formula? Thanks for responding - I can tell I'm closer than I was!

[jason.b75]

If you're getting a #DIV'0! error then that probably means that the array is not confirmed correctly.

Double click in the cell with the formula, then hold down Shift + Ctrl and press enter.

I Had the formula in B20, but it can go anywhere.

[kydan54]

Hey jason.b75! You were correct - I must have not confirmed the array correctly! Numbers match my manual operation perfectly! I'll send it on and find out if it works for him! Worked like magic! Appreciate it!