Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 0 and Blank fields.
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 0 and Blank fields.
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 0 and Blank fields. | Excel Discussion (Misc queries) | |||
save an excel file in fixed length records whose fields are blank | Excel Discussion (Misc queries) | |||
existing excel file when clicked opens blank excel document | Excel Discussion (Misc queries) | |||
?? Extra blank lines in 'address' cell after exporting to Excel | Excel Discussion (Misc queries) | |||
No Smart Tag help: just a blank "MS Excel Help" window - Excel 2003 | Excel Discussion (Misc queries) |