Rectified formula neded

[hsandeep]

I am using

AFZ53=IF(AND(AFB53<>0,AFB53<$AFO53),-100,IF(AFA53>$AFN53,100,0)) but getting 100 (This is WRONG) I should get 0. What should the rectified formula be?

AFA53=FALSE
AFB53=FALSE
AFN53=2.5
AFO53=-2.5

 

[Michael M]

TRUE / FALSE will take precedence over the value it's compared to.
Change the formula to

=IF(AND(AFB53<>0,AFB53<$AFO53),-100,IF($AFN53>AFA53,100,0))

 

[Michael M]

This may help.....


Example 3. Comparing Boolean values and numbers
There is a widespread opinion that, in Microsoft Excel, the Boolean value of TRUE always equates to 1 and FALSE to 0. However, this is only partially true, and the key word here is "always" or more precisely "not always" : )

When writing an 'equal to' logical expression that compares a Boolean value and a number, you need to specifically point out for Excel that a non-numeric Boolean value should be treated as a number. You can do this by adding the double minus sign in front of a Boolean value or a cell reference, e. g. =A2=--TRUE or =A2=--B2.

The 1st minus sign, which is technically called the unary operator, coerces TRUE/FALSE to -1/0, respectively, and the second unary negates the values turning them into +1 and 0. This will probably be easier to understand looking at the following screenshot:
Comparing Boolean values and numbers

 

[AlphaFrog]

Cannot make a direct comparison of Boolean (True\False) values to Numeric values. One way to convert Boolean to Numeric is to multiply them by 1

AFZ53=IF(AND(AFB53*1<>0,AFB53*1<$AFO53),-100,IF(AFA53*1>$AFN53,100,0))

 

[Michael M]

So does that negate the need for the double unary, as per my previous post ??

 

[XOR LX]

But the double-unary is simply one of several mathematical operations which will coerce Boolean values to their numerical equivalents. Multiplication by unity (as used by AlphaFrog), addition of zero and division by unity are three more examples.

It's always slightly surprised me that, of these choices, the double-unary is perhaps the most widely-used, especially given that, of those choices, it is also perhaps the least intuitive.

Regards

 

[Michael M]

Thanks for the input.....always glad to learn from others...

 

[XOR LX]

I'd join you for that beer if it wasn't 8am here! Cheers, anyway!

 

[Michael M]

....why can't you have a beer at 8am....

 

[XOR LX]

Haha! I should've seen that I was talking to an Aussie! There's no time that isn't beer time, eh?