How does Excel recognise a blank cell within IF function...

Thanks:  0
Likes:  0

Thread: How does Excel recognise a blank cell within IF function...

1. How does Excel recognise a blank cell within IF function...

Hi All,

How can i return a blank cell if the formula is looking at a blank cell with IF function?

E.g. IF(A1=" "," "," ")

(This is part of a bigger statement which returns a date if and when there is a date in the cell, if there isn't it comes up with an answer even though it should be blank)

Many thanks

2. Re: How does Excel recognise a blank cell within IF function...

Define "blank" cell?

Try one of the following:

=IF(A1="","Blank","Not blank")
=IF(ISBLANK(A1),"Blank","Not blank")

3. Re: How does Excel recognise a blank cell within IF function...

Hi

I think the problem may be that " " represents a Null field rather than a zero-length field, changing the condition to "" might solve the difficulty

Robert

4. Re: How does Excel recognise a blank cell within IF function...

Thanks both,

This is the other part of the formula...

=IF(A1>TODAY(),"Y","N")

Now i need to add - If A1 is blank i.e. has no data in it, i want A2 to be blank also (A2 contains the formula)

Hope that helps...

edit: sorry having trouble getting the formula to show up.... - Got it...

5. Re: How does Excel recognise a blank cell within IF function...

In A2 then:

=IF(A1="","",myformula)

6. Re: How does Excel recognise a blank cell within IF function...

Not working

If A1 is blank i want A2 (cell with formula in) to be blank also

and

If A1 (date) is greater than A3 (date) then i want it to show "Y" or "N"

I can understand the 2nd part and have that working but i just cant get an empty cell in A1 to return an empty cell in A2, and dont know how to add another IF into an IF formula!

I hope that clarifies a little

PS if have this so far... =IF(A1="","","")(A1>TODAY(),"Y","N")

7. Re: How does Excel recognise a blank cell within IF function...

This formula in A2:

=IF(A1="","",IF(A1>A3,"Y","N"))

If that doesn't work, then tell me what it DOES do.

What's in A1? A manually entered date?

8. Re: How does Excel recognise a blank cell within IF function...

You got it - works as it should thank you very much indeed

(And no its not a manually entered date in A1)

9. Re: How does Excel recognise a blank cell within IF function...

Hi all. This thread was the closest thing I could find to the problem I am having. The solution shown on this page did not work for me.

I'm creating an encrypting "program" where you enter a word or group of words and a lookup command will retrieve the letter typed and change it to a number.
My problem is that as the formula stands now, a single word works fine and any cells after the numbered cells (after encryption) remain blank. BUT if there are a group of words, every blank cell in between the encrypted words returns an N/A#. I don't know why those would cause the error when the rest of the line does not.

Here is what I've got (please note that I did not copy/paste the entire LOOKUP section) and notice the error at G18:

******** ******************** ************************************************************************>
 Microsoft Excel - Cryptactic.xls ___Running: xl2002 XP : OS = Windows XP
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 A17B17C17D17E17F17G17H17I17J17K17L17M17N17O17P17Q17R17S17T17U17V17W17X17Y17Z17A18B18C18D18E18F18G18H18I18J18K18L18M18N18O18P18Q18R18S18T18U18V18W18X18Y18Z18 =

A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
12
stupid error
13

14
ENCRYPT
15

16

17
stupid error

18
788743#37767

19

20

21

22

23

24

25
A2
26
B2
27
C2
28
D3
29
E3
 Sheet1

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

10. Re: How does Excel recognise a blank cell within IF function...

Hi Steve

every blank cell in between the encrypted words returns an N/A#.
If I understand correctly there are no blank cells in between words.

You are using the MID() function to extract the characters and so betweeen words you have a space.

Have you included a space in your conversion table? How do you want to deal with spaces?

P. S.

Next time it would be better to open a new thread, as this is a new problem. You could include a link to this thread.

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•