*OR statement

[Angelo P]

I have the function below in a cell that I'm trying to understand, but the person who wrote it no longer exists in our company.
Here it is:

IF((M5="I")*OR(M4="P"),"",IF($M$6<40,1,ROUNDUP(M6/40,0)))

The * before the OR has me confused. Is it a wildcard or is it creating an AND/OR statement or is it an XOR.

I have cell M5 that will accept an I or E, and a cell M4 that accepts a P or E, and then the above function goes to work, except it only leaves the cell empty when both the letter I and P are in their respective cells. So it acts more like an AND statement. It sure is a complicated way to do an AND, so I'm wondering if it does more than just AND?

Please assist.

 

[PaulF]

=IF(OR(M5="I",M4="P"),"",IF($M$6<40,1,ROUNDUP(M6/40,0)))

What is the complete logic you need to accomplish. The logic of this statement is sound...

What is the logic for P and I on M4 & M5 ??

 

[Hui]

You can ignore the or and simply use:
=IF((M5="I")*(M4="P"),"",IF($M$6<40,1,ROUNDUP(M6/40,0)))

All or is doing is converting the equality to a True/False, but (M4="P") is equivalent to or(M4="P")
Try it in a blank sheet


In fact the whole section (M5="I")*(M4="P")
can be simplified to And(M5="I", M4="P")
as it requires both to be True to return True

 

[PaulF]

=IF((M5="I")*(M4="P"),

Hui, I've done a search and can't seem to find an explanation on this. I don't understand the how the 2 items separated by an * works within the IF function... *scratcheshead*

 

[NARAYANK991]

Hi ,

The syntax for the IF function is :

=IF( test to see if a condition is satisfied , output if the condition is satisified , output if the condition is not satisfied )

Thus , an IF function has 3 parameters. However , Excel makes only the first parameter mandatory ; the remaining 2 parameters can be omitted if you do not need them , in which case , Excel will use the default value of FALSE for the missing parameters.

In Boolean Logic , TRUE evaluates the same as 1 , while FALSE evaluates the same as 0 , though the two are not equivalent ; thus TRUE is NOT the same as 1 , and FALSE is NOT the same as 0.

To test the above , put the value TRUE in A1 , and the value 1 in B1 ; in C1 , put in the formula :

=(A1=B1)

and see what is displayed.

Try with the values FALSE in A1 and 0 in B1.

However , Excel like any programming language , provides a way to convert a real Boolean value such as TRUE / FALSE to a numerical value. You can do this conversion by either multiplying by 1 or by adding 0 , since both of these mathematical operations will not change the value of the variable which is being used in the multiplication or addition.

Thus , in the above example , in D1 put in the formula :

=(A1 + B1)

Now , enter the values TRUE , FALSE in A1 , and the value 0 in B1 , and see what D1 displays.

Change the formula in D1 to :

=(A1 * B1)

and again check what D1 displays when you put in the values TRUE / FALSE in A1 and the value 1 in B1.

To be continued ...

Narayan

 

[NARAYANK991]

Hi ,

To come back to the IF function , try out the following function in any unused cell :

=IF(ISEVEN(2))

What this is doing is entering a test , which is to check whether the number 2 is even ; we know this is true.

However , Excel doesn't even accept this as a valid formula.

Let us try :

=IF(ISEVEN(2),)

Now Excel does recognize this as a valid IF formula , and displays the result , but the result is not what we would have expected !

Try changing the above formula to :

=IF(ISODD(2),)

We know this to be false , and it is gratifying when Excel agrees with us !

However , the reason Excel displays FALSE is different from what we think ; Excel displays FALSE in this case because we have not specified the output when the tested condition is not satisified i.e. if the test returns a FALSE result , and by default Excel uses the FALSE value for this.

This is a similar reason why when we entered :

=IF(ISEVEN(2),)

Excel displays 0 ; 0 is NOT the equivalent of TRUE , but since we have not specified what the output should be , Excel uses the default value of 0.

You may ask why 0 ? Why not FALSE ?

The reason is that when the parameter is omitted , Excel uses FALSE , and when the parameter is not specified , Excel uses 0.

The difference will not be clear now , but if we change the formula slightly , it will become clear.

Try first :

=IF(ISODD(2),)

See what is displayed ; we know that 2 is NOT ODD , and hence the test result will be FALSE ; however , we have omitted the 3rd parameter altogether.

Now try :

=IF(ISODD(2),,)

See what is displayed ; here the 3rd parameter has not been omitted ; instead it has not been specified.

To be continued ...

Narayan