Average Function Needed

[cdstudent]

I am making a runners log in Excel10. Column names are Mon:Fri B1:H1 and Sum I1.

I want to average the weeks (the sum column), but only for the weeks that have passed meaning each day for that week has a number entered (zero included). So I have

Rows underneath I1: =SUM(B2:H2) =SUM(B3:H3) etc... =SUM(B14:I14)

The most recent function I tried was =AVERAGE(IF(B2:H14<>0,I2:I14,FALSE))

This clearly didn't work and was averaging weeks prematurely. Thanks for your time and help.

 

[barry houdini]

Hello cdstudent

If you change the sum formula to return a blank when that week isn't completed then the average is simple.....

change I2 to this formula copied down

=IF(COUNT(B2:H2)=7,SUM(B2:H2),"")

Now average ignores blanks so you can use something like

=AVERAGE(I2:I100)

Of course that means you won't get a sum for each week until that week is completed is that OK?

If not then stick with your original SUM formula and only average weeks when the last day is filled in

=IF(ISNUMBER(H2:H100),I2:I100)

confirmed with CTRL+SHIFT+ENTER

 

[cdstudent]

First thank you for your help.

Now I would use the first solution, but I would really prefer a to have the sum kept current throughout the week.

The second solution given appears to be returning only the average (which would be the sum) of the first week instead of averaging all of the completed weeks together.

 

[barry houdini]

actually I didn't supply the correct formula...., should be

=AVERAGE(IF(ISNUMBER(H2:H100),I2:I100))

confirmed with CTRL+SHIFT+ENTER

 

[cdstudent]

That function comes close to solving the issue, but it is averaging all of the cells beneath the Sum column, including weeks in the future. So if I had a 10 week log and 2 weeks had passed with a sum of 20 miles per week, the weekly average would calculate as 3.1 instead of 20.

 

[barry houdini]

I'm not sure you are entering the formula correctly, does it display with { and } around the formula?

If not then do this:

select cell with formula
press F2 key to select formula
hold down CTRL and SHIFT keys and press ENTER
curly braces like { and ] should appear around the formula in the formula bar

 

[cdstudent]

The issue has been resolved! Thanks for the function, I really appreciate your help.