IF formula error

Alan Secker · Aug 8, 2003

I'm getting the wrong values out of the following formula.
The variable to plug in are:

E16 == 40000
B32 == 5000

The IF statement is:

=IF(E16 > B32 & E16 > 60000, 30000, E16-B32)

It should give, from left to right:

E16 > B32 returns TRUE
But
B32 is not > 60000 so it returns FALSE
So Result is not 30000
but
E16 - B32 which is 35000

but I get 30000

I'm clearly handling the & wrongly. Would someone be kind enough
to put me straight?

TIA

Alan Secker

Tom Ogilvy · Aug 8, 2003

your critieria returns a string

TrueTrue or FalseTrue or TrueFalse

and is done with the And function

IF(And(E16 > B32,E16 > 60000), 30000, E16-B32)

Matt. · Aug 8, 2003

I think you need to change the "&" to the word AND.

B32 & E16 will concatenate to 500040000

cheers,
Matt.

Tom Ogilvy · Aug 8, 2003

& is a concatenation operator in VB as well.

Regards,
Tom Ogilvy

Alan Secker · Aug 8, 2003

Oh!

Thanks Dan E

Dan said:
& is the correct operator in VB, but not in worksheet functions.

Try
=IF(AND(E16 > B32, E16 > 60000), 30000, E16-B32)

Dan E

Alan Secker · Aug 8, 2003

Thanks Matt. Alan

Matt. said:
I think you need to change the "&" to the word AND.

B32 & E16 will concatenate to 500040000

cheers,
Matt.