aauugghhh...#div/o problems & various average formula problems

[acbel40]

Help...I'm confusing myself...

Trying to determine an average per month, based on 12 months of numerical
data…but some of the cells have zero’s. Now…I need to divide the sum of
those 12 cells by 12 IF they ALL have numbers greater than 0. If any of the
12 cells has a 0…then I need to divide the sum of the 12 cells by the number
of cells that actually have a whole number. I.E.
13 13
2 2
3 0
4 4
5 = 27/5 = 5.4 5 = 24/4 =6 (This is the answer I need)


Been using the array formula: =AVERAGE(IF(I6:I17<>0, I6:I17,""))…which works
fine…. But some of the columns have ALL zero’s….and of course I get the
#DIV/0 error, so I tried the =SUM(I6:I17)/COUNTIF(I6:I17,">0") on all the
columns, didn’t work (divides the sum of the cells by ALLS including the zero
cells)…so I need to use ONE array formula, plus something??? to give me a
zero total on the zero column. Because it affects the formula I use later to
calculate Quarterly averages….(maybe I need to do a format change???)
Yep...I am confused…

Whew….anyone can help me with this…I’d be truly grateful….

 

[FloMM2]

acbel40,
Try this solution (it worked for me):
In the cell that is for the average, put this formula:
"=(SUMIF(A1:A5,">0")/COUNTIF(A1:A5,">0"))"
A1 = 13
A2 = 2
A3 = 3
A4 = 5
A5 = 5
A6 will be 5.4 (format cell to 1 decimal place).

Both halves of the formula only adds up the numbers larger than 0.

hth

 

[JoeU2004]

Been using the array formula: =AVERAGE(IF(I6:I17<>0, I6:I17,""))…
which works fine…. But some of the columns have ALL zero’s….and
of course I get the #DIV/0 error

If you know that the size of the range is 12, and all cells contain numeric
values, then:

=if(countif(I6:I17,0)=12, 0, average(if(I6:I17<>0, I6:I17)))

More generally:

=if(countif(I6:I17,0)=count(I6:I17), 0, average(if(I6:I17<>0, I6:I17)))

Both should be entered as an array formula. That is, commit with
ctrl+shift+Enter instead of Enter.


----- original message -----

 

[acbel40]

Thank you....but I'm averaging the columns...will this formula work
(substituting SUMIF with AVERAGEIF?

 

[acbel40]

That was a dumb question...I tried it...but in the column with all zeros...it
gives me the #div/0 error now (works fine on columns with whole numbers)

 

[Dave Peterson]

Check first...

=if(countif(a1:a5,">"&0)=0,"No numbers > 0",sumif(...)/countif(...))