Simple question

Girish · May 13th, 2002, 03:06 PM

I am calculating the difference between two cells, but if both cells are blank it returns me 0.
How can I make it return FALSE if both cells are blank.

Thanks !
Girish

Nimrod · May 13th, 2002, 03:08 PM

Assuming your evaluating A1 and B1 then this would be it:

Code:

=IF(A1+B1=0,"False",A1+B1)

_________________
NOTE: Testing performed on Win2K utilizing Office 2000. Solutions may need tweaking for other versions.

[ This Message was edited by: Nimrod on 2002-05-13 14:09 ]

Mark W. · May 13th, 2002, 03:10 PM

=IF(LEN(A1&B1),A1-B1)

[ This Message was edited by: Mark W. on 2002-05-13 14:13 ]

Mark W. · May 13th, 2002, 03:12 PM

Quote:

On 2002-05-13 14:08, Nimrod wrote:
Assuming your evaluating A1 and B1 then this would be it:

Code:

=IF(A1+B1=0,"False",A1+B1)

Careful! What if A1 contains -1 and B1 contains 1?

Girish · May 13th, 2002, 03:17 PM

Thanks for the quick responses.
I would like to modify the question a little..

I want FALSE even if either cell is blank.

Aladin Akyurek · May 13th, 2002, 03:20 PM

Quote:

On 2002-05-13 14:06, Girish wrote:
I am calculating the difference between two cells, but if both cells are blank it returns me 0.
How can I make it return FALSE if both cells are blank.

Thanks !
Girish

=IF(COUNTBLANK(A1:B1),FALSE,diff)

where diff either A1-B1 or B1-A1.

Mark W. · May 13th, 2002, 03:21 PM

=IF(OR(ISBLANK(A1),ISBLANK(B1)),FALSE,A1-B1)

Or...

=IF(AND(LEN(A1),LEN(B1)),A1-B1)

[ This Message was edited by: Mark W. on 2002-05-13 14:26 ]

Nimrod · May 13th, 2002, 03:23 PM

Carefull Mark... what if the cell has the value zero in it .

Code:

=IF(OR(A1=0,B1=0),"False",A1-B1)

This one will work for both blank cells and a zero value
_________________
NOTE: Testing performed on Win2K utilizing Office 2000. Solutions may need tweaking for other versions.

[ This Message was edited by: Nimrod on 2002-05-13 14:25 ]

Aladin Akyurek · May 13th, 2002, 03:28 PM

Quote:

On 2002-05-13 14:17, Girish wrote:
Thanks for the quick responses.
I would like to modify the question a little..

I want FALSE even if either cell is blank.

Try the COUNTBLANK version.

Mark W. · May 13th, 2002, 03:29 PM

Quote:

On 2002-05-13 14:23, Nimrod wrote:
Carefull Mark... what if the cell has the value zero in it . [img]/board/images/smiles/icon_wink.gif[/img]

Code:

=IF(OR(A1=0,B1=0),"False",A1-B1)

This one will work for both blank cells and a zero value

...But, what if A1 contains 0 and B1 contains 1? It'll return FALSE instead of -1.

May 13th, 2002, 03:08 PM	#2
Nimrod MrExcel MVP Join Date: Apr 2002 Location: Vancouver BC , Canada Posts: 6,259	Assuming your evaluating A1 and B1 then this would be it: Code: =IF(A1+B1=0,"False",A1+B1) _________________ NOTE: Testing performed on Win2K utilizing Office 2000. Solutions may need tweaking for other versions. [ This Message was edited by: Nimrod on 2002-05-13 14:09 ]

May 13th, 2002, 03:23 PM	#8
Nimrod MrExcel MVP Join Date: Apr 2002 Location: Vancouver BC , Canada Posts: 6,259	Carefull Mark... what if the cell has the value zero in it . Code: =IF(OR(A1=0,B1=0),"False",A1-B1) This one will work for both blank cells and a zero value _________________ NOTE: Testing performed on Win2K utilizing Office 2000. Solutions may need tweaking for other versions. [ This Message was edited by: Nimrod on 2002-05-13 14:25 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 13th, 2002, 03:06 PM	#1
Girish New Member Join Date: May 2002 Posts: 14	I am calculating the difference between two cells, but if both cells are blank it returns me 0. How can I make it return FALSE if both cells are blank. Thanks ! Girish

May 13th, 2002, 03:10 PM	#3
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	=IF(LEN(A1&B1),A1-B1) [ This Message was edited by: Mark W. on 2002-05-13 14:13 ]

May 13th, 2002, 03:17 PM	#5
Girish New Member Join Date: May 2002 Posts: 14	Thanks for the quick responses. I would like to modify the question a little.. I want FALSE even if either cell is blank.

May 13th, 2002, 03:21 PM	#7
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	=IF(OR(ISBLANK(A1),ISBLANK(B1)),FALSE,A1-B1) Or... =IF(AND(LEN(A1),LEN(B1)),A1-B1) [ This Message was edited by: Mark W. on 2002-05-13 14:26 ]