AVERAGE issue

[Randy Starkey]

Hi,

I'm averaging 52 rows which are week totals. I have a result column for the
average. Problem: If I have only say 30 of the 52 weeks currently to
average, the formula that does my week totals has a zero in the week rows
that haven't happened yet, so my running average is way too low due to the
zeros. Is there a way to include an if condition for the averaging, like
include this row IF the value is greater than zero?

Or, is there some simpler way to solve this? I could of course not include
the totals formulas until that week has data, which would give me blank
cells for unused weeks, but I hate to have to copy and add formulas to each
week as data is entered. Data entry for volunteers is enough <g>.

Thanks!

--Randy Starkey

 

[Bernard Liengme]

=AVERAGE(IF(A1:A100>0,A1:A100)
will work when entered as an array function with CTRL+SHIFT+ENTER not just
Enter

If you have XL2007 look at AVERAGEIF

If you have the data in table from, then when you add new data the formula
should automatically add to the appropriate cell
1 formula
3 formula
4 formula
5 formula
Now when I enter a number below 5, the formula should copy to the right cell
in Excel versions 2002 and later. You need 4 rows with the data/formula for
Excel to 'learn' what is needed.

best wishes

 

[Bill Kuunders]

for example
enter the formula below to find the weeks' total

=IF(SUM(C2:C34)=0,"",SUM(C2:C34))

 

[Randy Starkey]

Thanks! I'll experiment. I'm in Excel 2003.

--Randy

 

[tedjillett]

Works like a charm...I knew I was missing something