MrExcel Publishing
Your One Stop for Excel Tips & Solutions

=if function

Posted by Ablation on April 27, 2001 6:46 AM

If I enter anything in cell n# I would like the function cell to return a 1.
If both cells have anything in them "n14 and o14" returns a 0. Blanks also return 0. I got the code working with single cells but I cant get it to work with all the cells I need it on.

(working code)
(not working)
=IF(N14:N100="",0,IF(N14:N100<>"",IF O14:O100="",1,0)))

Posted by Jerid on April 27, 2001 7:39 AM

Hi Ablation

Try this


Posted by Ablation on April 27, 2001 8:27 AM

Re: Nope

Well, you caught the missing ( but the extra ""'s didnt work....

Posted by Michelle on April 27, 2001 10:21 AM

Have you tried:



Posted by Jerid on April 27, 2001 10:25 AM

Try this, make sure after you type this in (or copy and paste) you hold the Ctrl & Shift keys down when you hit enter, or this formula will not work.
=IF(AND(N14:N100 = "",O14:O100 = "")=TRUE,0,1)


Posted by Ablation on April 27, 2001 11:06 AM

Those both worked better but the only cell I want a "1" for is if N has anything in it while O can negate it.
for instance:

A--------- =1 (N1 has "A" / O1 nul)
A--------A =0 (N2 has "A" / O1 "A")
---------A =0 (etc)
---------- =0
(hard to create a graphic pic here.)
It would be nice if the "1" could count but thats beyond my limit.thx

Posted by Aladin Akyurek on April 27, 2001 11:13 AM

Array-enter (that is, hit CONTROL+SHIFT+ENTER at the same to enter)


You may substitute LEN(O14:O100)=0 for ISBLANK(O14:O100) in the above formula.

Hope I understood you correctly.