If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
existing excel file when clicked opens blank excel document | snows_24 | General Discussion | 1 | May 21st, 2005 07:15 PM |
Blank Fields in reports | Jean | Setting Up & Running Reports | 5 | April 1st, 2005 01:15 PM |
Closing up Blank fields on a report | Matthew Wayne | Setting Up & Running Reports | 1 | January 28th, 2005 02:33 AM |
Prevent Blank Records being written. Need Help. | Robert Nusz @ DPS | Using Forms | 4 | December 29th, 2004 05:15 PM |
Macro help please: Excel data to word doc | anna-maria | General Discussion | 5 | June 30th, 2004 11:53 PM |