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

[papichulo184]

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

 

[Yard]

Define "blank" cell?

Try one of the following:

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

 

[Robert E Lee]

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

 

[papichulo184]

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...

 

[Yard]

In A2 then:

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

 

[papichulo184]

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")

 

[Yard]

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?

 

[papichulo184]

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

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

 

[steve the excel guy]

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:

Cryptactic.xls
	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	stupiderror
13
14	ENCRYPT
15
16
17	s	t	u	p	i	d		e	r	r	o	r
18	7	8	8	7	4	3	#	3	7	7	6	7
19
20
21
22
23
24
25	A	2
26	B	2
27	C	2
28	D	3
29	E	3
Sheet1

 

[pgc01]

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.