empty cell as a result of IF formula

  • Thread starter Thread starter Jeremy BZH
  • Start date Start date
J

Jeremy BZH

i need to obtain an empty cell as a result of a "IF" formula, i usually use
=if(logical test;"";value_if_false)
but i cannot use this result in a SUM formula
 
Jeremy BZH said:
i need to obtain an empty cell as a result of a "IF" formula, i usually
use
=if(logical test;"";value_if_false)
but i cannot use this result in a SUM formula

Perhaps you want:

=if(condition, "", SUM(range))

Note that this does not result in an "empty cell" per se. In particular, if
that formula is in A1 and it results in "", ISBLANK(A1) will be false(!).

A formula can only result in the null string, which makes the cell
__appear__ empty or blank. You can detect truly empy (no formula or
constant) or null string by testing A1="".
 
i need to use this cell with the formula result ("") for another calculation
with SUM(), but this result is #VALUE!,at the same time time, i use it for a
Count() formula and it works (it doesnt count it).
can i use something else than "" that will work both with SUM and COUNT ?
 
If you are looking to deal with errors..then

=IF(ISError(your formula),"",your formula)

If this post helps click Yes
 
i need to get in cell 1 with IF formula a result that make it apear empty,
and use this cell 1 for a SUM formula in cell 2 and a count formula in cell
3. can i use something else than "" to make cell 1 appear empty ?
 
SUM formula will ignore blank without returning error. If you are trying
=A1+B1 that will return a #value error. To handle that you can try the below

=("0" & A1)+B1
or try
=SUM(A1:B1)

'You havent yet posted your formula which returns the error.

If this post helps click Yes
 
this is a simple formula like =R64+R56+R48+R40+R32+R24+R16
but i have IF formula in all those cells like
=IF(COUNT(AB55:AB56)=0;"";IF(COUNT(AB55)>0;IF(COUNT(Q55:AB55)<$K$4;"";AB55*0,99);IF((COUNT(L56:AB56)+$K$4+4)<$K$5;AB56;"")))
this sometimes give answer "" that is not handles by the first formula
 
thats it !! i simply had to put the sum formula
=SUM(R63;R55;R47;R39;R31;R23;R15) !!!

thanks for your answers !! i'll try to think a little bit more next time .....
 
Jeremy BZH said:
i need to use this cell with the formula result ("") for another
calculation
with SUM(), but this result is #VALUE!,at the same time time, i use it for
a
Count() formula and it works (it doesnt count it).
can i use something else than "" that will work both with SUM and COUNT ?

SUM and COUNT ignore cells that have text (notably "") as well as empty
cells. And apparently, that is sufficient to solve your problem.

But for future reference, another feature to put into your bag of tricks is
the N() function. For example:

=n(A1)*n(A2) - n(A3)

can be used if A1, A2 and/or A3 might be "". N() returns zero if the
argument is text, or the number if the argument is numeric.

FYI, the only way I know to get Excel 2003 offline help information on the
N() function is to enter "text and data functions" (without quotes), click
on T worksheet function, then click on See Also.


----- original message -----
 
Back
Top