software

Problem:

When I enter the following equation into a cell with a blank in cell
CH12 or CH13 I get the correct answer (the sum of the cells with
numbers in them) but when there is a blank in cell CH14 I get an
incorrect answer (in the form of a clank cell).

=IF(AND(ISNUMBER(CH12:CH14)),SUM(CH12:CH14),quot;quot;)

Does anyone have any suggestions?

Thanks,
ElaineWhen CH14 is blank, ISNUMBER perceives it as a non-numeric so the IF is
returning the quot;falsequot; value, a blank. I'm trying to think of
workarounds, and without knowing the rationale for the ISNUMBER the
best I can think of is to enter a zero instead of a blank.Hi,
Thanks but I cannot use zero's. I have stepwise calculations on raw
data and I have to ensure that true zero's are kept in while empty
cells (with no raw data) are continuously considered blank (not
zero's). For the example above I cannot use a simple sum function
(which would work if I was only missing 1 or 2 cells of the range)
because if all three cells are missing/blank in the previous
calculation, they have to return a blank, not a zero.
Thanks,
ElaineRe-read your post: land the cell pointer on your formula and press F2
to edit, then convert it to an array formula by pressing
CTRL-SHIFT-ENTER. It will then behave as expected.If you want zero sum to be blank,

=IF(SUM(CH12:CH14)gt;0,SUM(CH12:CH14),quot;quot;)

quot;Dave Oquot; wrote:

gt; When CH14 is blank, ISNUMBER perceives it as a non-numeric so the IF is
gt; returning the quot;falsequot; value, a blank. I'm trying to think of
gt; workarounds, and without knowing the rationale for the ISNUMBER the
gt; best I can think of is to enter a zero instead of a blank.
gt;
gt;

I have tried changing it to an array but then will get a blank cell
whenever any of the 3 cells are blank.

I must be using the wrong formula - what I want to do is have a formula
that will sum any cells within the range that have a number but if ALL
of the cells are empty I need the formula cell to remain blank.You have other replies in .excel.
wrote:
gt;
gt; Problem:
gt;
gt; When I enter the following equation into a cell with a blank in cell
gt; CH12 or CH13 I get the correct answer (the sum of the cells with
gt; numbers in them) but when there is a blank in cell CH14 I get an
gt; incorrect answer (in the form of a clank cell).
gt;
gt; =IF(AND(ISNUMBER(CH12:CH14)),SUM(CH12:CH14),quot;quot;)
gt;
gt; Does anyone have any suggestions?
gt;
gt; Thanks,
gt; Elaine

--

Dave Peterson

in case anyone has a similar problem, I got this suggestion from Dave
Peterson and it works perfectly!

=if(count(sh12:ch14)gt;0,sum(ch12:ch14),quot;quot;)

the gt;0 can be replaced with whatever number you want before a summation
is done (i.e. 1 value, 2 values, etc. present)