Dave Thomas said:
Your formulas:
=IF(COUNT(AO19),SUM(AL4:AL18)/AO19,"")
=IF(COUNT(AO19),AL4/AO19,"")
=IF(COUNT(1/AO19),SUM(AL4:AL18)/AO19,"")
=IF(COUNT(1/AO19),AL4/AO19,"")
Mine:
=IF(AO19<>0,SUM(AL4:AL18)/AO19,"")
=IF(AO19<>0,AL4/AO19,"")
AO19 is supposed to contain a numeric including 0 or blank and nothing
else. . . .
Yes, that may be what they're SUPPOSED TO contain. If this is the OP's own
workbook for the OP's own exclusive use, how likely would it be that #DIV/0!
errors before entry would be a big concern? OTOH, if this were something the
OP were making for OTHERS to use, then all bets are off whether those others
will enter only what's supposed to be entered.
My formulas avoid the #DIV/0 error yet return an error if there is
something other than blanks or zeroes which should be fine. . . .
Your formula would return numbers if AO19 evaluated to nonzero numeric text
or boolean TRUE, and would return #DIV/0! if AO19 evaluated to "0" or
boolean FALSE. That's reasonable. However, users have an annoying tendency
to use the space bar to 'clear' cells. Up to the OP to decide whether or not
this matters.
. . . I would want to know if there is garbage in that cell. If there is
garbage in that cell your formulas hide the fact by returning an empty
string.
Reasonable. If this were for the OP's use, that'd make considerable sense.
If it's for other users, the OP may need to be somewhat more forgiving.
As regards the =IF(SUM(C4:C500)<>0,AVERAGE(C4:C500),"") I submitted.
Your suggestion of =IF(COUNT(C4:C500),AVERAGE(C4:C500),"") serves no good
purpose either, as it allows for non-numerics in the column.
Guess what? SUM and AVERAGE ignore nonnumeric cells too (as long as they
don't evaluate to errors). So your point is?
Let us consider the C4:C500. Let us work with a smaller range, A1:A10 and
name it DATA. Let us put the values 1,2,3,4,5,-1,-2,-3,-4,-5 in DATA, and
play with the values.
Good example. COUNT(DATA) = 10, SUM(DATA) = 0, AVERAGE(DATA) = 0. You seem
to be arguing that in this case it'd be a good thing to return "" rather
than the actual average 0. Why?
I propose this formula which handles blanks, negative numbers, 0's,
positive numbers and non-blanks in DATA:
=IF(COUNT(DATA)+COUNTBLANK(DATA)=ROWS(DATA),
IF((SUM(DATA)<>0)+COUNTIF(DATA,"<0"),AVERAGE(DATA),""),
"Houston, we have a problem")
The first IF adds something new: checking that all cells either contain
numbers are blank OR evaluate to "", since COUNTBLANK includes cells
evaluating to "" in its resulting count. Not unreasonable.
The second IF test fails to average all numberic cells evaluating to 0, in
which case the average is 0.
If you want error values returned if there are error values in DATA, you
want any cells evaluating to text other than "" to produce a diagnostic
message, you want "" if all cells are blank or evaluate to "", and you want
the average otherwise, try
=IF(COUNTIF(DATA,"=?*"),"trouble",IF(COUNT(DATA),AVERAGE(DATA),""))
This will calculate the average if DATA contains any boolean values along
with numbers, blanks and ""s. If you also want boolean values to trigger the
diagnostic message,
=IF(OR(COUNTIF(DATA,{"=?*";"TRUE";"FALSE"})),"trouble",
IF(COUNT(DATA),AVERAGE(DATA),""))
As I stated before, COUNT is the appropriate test. The COUNTIF test in my
formulas above tests for an additional condition of 'invalid', but nonerror
values in DATA. Using SUM in the test is *ALWAYS* a bad idea.
