Calculating Average

[Hairbear]

I have to create a spread sheet that will calculate the last rounds average. For example, if we are golfing for the next 10 weeks. I will be adding up the first 3 weeks (golf score) and dividing by 3 to get an average (50+49+48/3 = 49).

Then at the start of the 4th week (he golfed a 47) I need to drop the first week score (50) and add up the next 3 scores (49+48+47/3 = 48).

Here is the current formula I am using (which is not working) :

=SUM($D8:$W8)/COUNTIF($D8:$W8,">0")

[Paul]

Are you only going to want to average the last 3 scores at any given time?

You can use this formula in F9:W9 if that is the case:

=IF(ISERROR(SUM(D8:F8)/COUNTIF(D8:F8,">0")),"",SUM(D8:F8)/COUNTIF(D8:F8,">0"))

If you use Excel 2007, you should be able to use this newer IFERROR formula:

=IFERROR(SUM(D8:F8)/COUNTIF(D8:F8,">0"),"")

[Stuart Farr]

In row 6, for each column, create a formula that checks for a value greater than zero two columns to the right. i.e. in cell D6 put =IF(AND(D8>0,G8=0),1,0). Copy this formula across the columns.
Then in cell AA8 put =SUMIF(D6:W6,1,D8:W8)/3.
This should do it.
Stu