IF() Function Syntax Option

[Colin Legg]

Hi all,

IF(logical_test, [value_if_true], [value_if_false])

I noted today that in the helpfile topic for the IF() worksheet function it is possible to omit both the function's 2nd and 3rd arguments.

Although to do so you have to add a comma after the logical_test, eg:

=IF(A1="hello",)

When both [value_if_true] and [value_if_false] arguments are omitted the function always returns 0 if the condition is met or FALSE() if the condition is not met (unless the condition references an error value in which case an error is returned).

Can anyone think of an instance where using the IF() function like this could be remotely useful?

Note - I'm comfortable with leaving one of these arguments out, just can't think of a valid application for leaving both out. Just a feeling in the back of my mind that the fact that IF() in this format is able to return different data types (number vs logical depending on condition=TRUE vs FALSE) might mean that there could be a clever use for it?

Cheers,

 

[russellrichter]

Colin,

Very interesting. I never noticed that before.

My guess is that the IF() function evaluates two values, and returns a True (0) or False (FALSE) answer. This test without a ValueIfTrue/ValueIfFalse entry is essentially the equivalent of comparing the two values with an = operator. That is, =IF(A1=B1,) is the same as =A1=B1. The former returns a 0 or FALSE, whereas the latter returns a TRUE or FALSE.

The only reason I see for using IF() to test for equality between two values would be to impress your friends, amaze your enemies, and obfuscate the obvious.

JMHO
Russ

 

[Scott Huish]

I've known about this for quite a while, but until this post have not found a use for it, but I'm thinking the reason for 0 and FALSE is so that if you need a number for an array formula or FALSEs to be ignored in array formulas for example:

While you could do this with SUMPRODUCT, utilizing this you could do an array formula like so:

=COUNT(IF(LEFT(B1:B7,2)="AB",))

Confirm with CTRL-SHIFT-ENTER

 

[Norie]

No idea if it's relevant but =ISNUMBER(FALSE) returns, well, FALSE.

But then again you can co-erce with --.

 

[Colin Legg]

Thank you all for your replies.

I think HOTPEPPER's suggestion is most inline with the sort of application I was considering - use in a CSE formula where a function which reads (zero) numbers but ignores booleans is aggregating the result, thereby concisely excluding unwanted elements.

It struck me that 0 vs FALSE seems like a slightly strange choice of 'default' outputs. I think I would have expected 1 and FALSE which might have been more useful?

Cheers,

 

[Jonmo1]

Here's my take on it, probably not right...

=IF(A1="hello",)

I'm thinking that by putting the comma there, the [value if true] argument is technically NOT actually omitted, but is given a NULL value, therefore treated as 0.

The same thing happens if you do this

=IF(A1="hello",,) <--- Notice 2 commas.
Niether argument is techically omitted, just given a Null value. yout get 0 whether A1 = hello or not, instead of 0 for true, false for false


Not sure if that's right, but that's my take on it...

 

[Colin Legg]

Hi Jon,

Sorry for the slow reply.

I think that is a very lucid observation.

I was a little bit mislead by the [ ] around the 2nd parameter, but the details in the helpfile clearly state it is required. Thanks for picking up on that.




I have another IF() 'observation' and I'd be appreciative if someone could double check this for me.

In Excel 2007, I set up the below worksheet with some simple constants and IF() calls. I deliberately didn't CSE enter the formulas.

It seems that IF() returns a result relative to the row position of the formula. If the formula's row takes it 'out of bounds' then a #VALUE! error is returned. So, E7:E8 return an error because B1:B5 doesn't have 7 & 8 rows, but E9 is okay because B1:B10 has enough rows. Similarly D11 has an error because A1:A10 doesn't have 11 rows.

Please can someone check if the same thing happens in Excel XP/2003?

Excel Workbook
	A	B	C	D	E
1	1	a		a	a
2	2	b		b	b
3	0	c		FALSE	c
4	-1	d		d	d
5	5	e		e	e
6	0	f		FALSE	f
7	-3	g		g	#VALUE!
8	8	h		h	#VALUE!
9	0	i		FALSE	i
10	10	j		j	#VALUE!
11		k		#VALUE!	#VALUE!
12		l		#VALUE!	#VALUE!
13		m		#VALUE!	#VALUE!
14		n		#VALUE!	#VALUE!
Sheet1

Thanks

 

[Jonmo1]

I think that is just kind of a lazy man's function. I think it works in most simple functions. I tried it with a few, AND,IF,OR,EXACT,CONCATENATE.

Instead of having to type A and find the right Row#, a lazy person can just type A:A and it will refer to the row that the formula is entered in.

I think it is why some CSE formulas will still return a value (the wrong value) and not an error when NOT entered with CSE.

 

[Colin Legg]

I have my own mad "ideas" on this so (although most welcome) I'm not specifically looking for an explanation of the behaviour as such. Unfortunately I don't have Excel 2000/2002/2003 at home (which I need for my purposes) - so what I'm particularly hoping for is confirmation that this set-up produces the same or different results on these earlier versions?

Thanks

 

[Jonmo1]

yes, I'm on 2003 and it works..