Excel 0 and Blank fields.

I am working on a spreadsheet that is relatively simple, but I need to have
empty cells and cells input with a 0 to give a referenced cell different
values. I have used the following formulas to do this for a cell with a 0
in it but I have found that this interprets an empty cell the same as a cell
with a 0 in it.



Cell C2 Ex.1: =IF (A2+A3=0), " " , SUM (A2:A3)

RESULTS: If I enter a 0 in A2 or A3 or if I leave A2 or A3 empty the
returned value will be blank.



Cell C2 Ex.2: =IF (A2+A3= " " ), " " , SUM (A2:A3)

RESULTS: I get the same results as Ex.1.




A
B
C

1
Input Data

Output Data

2




3
00








What I need is: IF (A2 AND A3= Blank ) THEN return blank, ELSE SUM (A2:A3)
[even if the value(s) entered into A2 and/or A3 is/are 0 or any combination
of zeros and blanks, I need it to return a 0.]



Thanks for any input.

Try:

=IF(OR(ISNUMBER(A2),ISNUMBER(A3)),SUM(A2:A3)," ")

HTH


" wrote:



I am working on a spreadsheet that is relatively simple, but I need to have
empty cells and cells input with a 0 to give a referenced cell different
values. I have used the following formulas to do this for a cell with a 0
in it but I have found that this interprets an empty cell the same as a cell
with a 0 in it.



Cell C2 Ex.1: =IF (A2+A3=0), " " , SUM (A2:A3)

RESULTS: If I enter a 0 in A2 or A3 or if I leave A2 or A3 empty the
returned value will be blank.



Cell C2 Ex.2: =IF (A2+A3= " " ), " " , SUM (A2:A3)

RESULTS: I get the same results as Ex.1.




A
B
C

1
Input Data

Output Data

2




3
00








What I need is: IF (A2 AND A3= Blank ) THEN return blank, ELSE SUM (A2:A3)
[even if the value(s) entered into A2 and/or A3 is/are 0 or any combination
of zeros and blanks, I need it to return a 0.]



Thanks for any input.

Hi,

Try this formula:

=IF(AND(A2="",A3=""),"BLANK",SUM(A2:A3))

If you enter a zero into A2 or A3, you will see a zero is returned, but if
you do not enter any data into A2 or A3, you will see BLANK is returned.

Ewan.

" wrote:



I am working on a spreadsheet that is relatively simple, but I need to have
empty cells and cells input with a 0 to give a referenced cell different
values. I have used the following formulas to do this for a cell with a 0
in it but I have found that this interprets an empty cell the same as a cell
with a 0 in it.



Cell C2 Ex.1: =IF (A2+A3=0), " " , SUM (A2:A3)

RESULTS: If I enter a 0 in A2 or A3 or if I leave A2 or A3 empty the
returned value will be blank.



Cell C2 Ex.2: =IF (A2+A3= " " ), " " , SUM (A2:A3)

RESULTS: I get the same results as Ex.1.




A
B
C

1
Input Data

Output Data

2




3
00








What I need is: IF (A2 AND A3= Blank ) THEN return blank, ELSE SUM (A2:A3)
[even if the value(s) entered into A2 and/or A3 is/are 0 or any combination
of zeros and blanks, I need it to return a 0.]



Thanks for any input.