Conditional averaging based on previous five numbers spread over 19 cells

[adatom]

Hello again,

After countless hours at work (8), trying to fix my Golf League program, I have decided to come back to Mr. Excel again!

I'm currently averaging every score each golfer shoots and use 80% of that for their handicap.

What I want to do:

A formula to lookup the last five rounds a player shot, this might not be the last five weeks, and calculate averages this way.

Example
wk 1 2 3 4 5 6
Player 1 38 45 48 39 42
Player 2 40 45 47 48 42 46

Player 1, the calculation would need to know that he didnt play during week 3, that cell would be blank, not zero and calculate using weeks 1-6

Player 2, He played all six weeks and his average would be calculated from weeks 2-6

I havent figured out how to attach an excel file but if needed I can provide. I've tried COUNTIF( Index( Match( .


Thanks in advance!!!

 

[barry houdini]

You could use this formula to average the last 5 values in B2:T2 (or all values if there are less than 5)

=IF(COUNT(B2:T2)<5,AVERAGE(B2:T2),AVERAGE(IF(COLUMN(B2:T2)>=LARGE(IF(ISNUMBER(B2:T2),COLUMN(B2:T2)),5),IF(B2:T2,B2:T2))))

confirmed with CTRL+SHIFT+ENTER

...or another possibility in Excel 2007 or later versions

=IFERROR(AVERAGE(INDEX(B2:T2,IFERROR(LARGE(IF(ISNUMBER(B2:T2),COLUMN(B2:T2)-COLUMN(B2)+1),5),1)):T2),"")

also with CTRL+SHIFT+ENTER

 

[adatom]

It works, however I am pulling the information from another workbook which is faulting out the equation due to "" being in cells without a value (ie. weeks we havent played or missed), thoughts on an addition to the above equation to make it ignore "" cells?