# Conditional averaging based on previous five numbers spread over 19 cells

##### New Member
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( .

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
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

Last edited:
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?

Replies
4
Views
1K
Replies
41
Views
1K
Replies
8
Views
501
Replies
0
Views
831
Replies
5
Views
261

1,203,400
Messages
6,055,179
Members
444,768
Latest member
EMGVT

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back