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.
These seem to satisfy your requirement
=IF(AND(ISBLANK(A2),ISBLANK(A3)),"",A2+A3) =IF(AND(ISBLANK(A2),ISBLANK(A3)),"",SUM(A2:A3)) Note (a) SUM(A2:A3) is overkill for A2+A3 unless you need to get a numeric result even when one cell has text (b) A cell with a formula such as =IF(MONTH(TODAY()) =3, "", MONTH(TODAY())) which displays a blank in March will still fail the ISBLANK test because a cell with a formula is not blank even when it displays a blank (clear?) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email wrote in message ... 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.] |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 0 and Blank fields.
Thanks all for the info. All 3 formulas seem to proof out correctly. Sorry
for the multiple posts though. I was getting an error message saying it couldn't connect to the news server. wrote in message ... 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 | |||
save an excel file in fixed length records whose fields are blank | Excel Discussion (Misc queries) | |||
Not including blank fields in pivot tables | Excel Worksheet Functions | |||
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) |