IF Stmt Translation:

[giuls21]

=IF($B$60,IF($B$27=0,(ABS(CN$66)*(CO$64-1))+CN66-CO$38,IF($H$59="Leveraged Real",(ABS(CN$66)*(CO$64-1))+CN66-CO$31,(ABS(CN$66)*(CO$64-1))+CN66-CO$38)),"N/A")
I am having a hard time understanding what exactly this means. Is anyone able to translate into simple terms for an excel newbie?

Thank you!
James

 

[MDCurry]

Hello,
Not knowing what the data is referencing makes it difficult to explain what is happenning.

Looks like there is an error though since the 1st IF condition =IF($B$60 does not have an operator. Are you looking to compare B60 to a value need a = or <> after this.

There are two ways I'd look at this to simplify:

1st approach is divide and conquer. With multiple IF statements it is easier to understand by highlighting smaller sections of the If statement and copying it into other cells to see what the results are for the given sections

2nd approach - Use F9 key to return the results of pieces of the formula. That is highlight a section of the formula and hit F9 to see what the reference returns. This way you can see the actual numbers being evaluated and follow the logic chain step by step. Given your Example suppose cell B27=0

In your formula highlight $B$27 and hit F9....it should convert $B$27 to 0 so now you'll see 0=0...highlight 0=0 and hit F9 and you'll see TRUE showing you that the formula will perform the TRUE condition of the IF statement....and so on

 

[shg]

You can also use the Evaluate Formula button to watch the formula evaluate step by step.

I think it could be simplified to

=IF($B$60=0, "N/A", ABS(CN$66)*(CO$64-1)+CN66 - IF(OR($B$27=0, $H$59<>"Leveraged Real"), CO$38, CO$31))

 

[Scott Huish]

Looks like there is an error though since the 1st IF condition =IF($B$60 does not have an operator. Are you looking to compare B60 to a value need a = or <> after this.

There doesn't have to be an operator.

IF evaluates whether an expression evaluates to TRUE or FALSE.

Numerically speaking, 0 evaluates to FALSE, any other number evaluates to TRUE, thefore

=IF(B60,TRUE,FALSE)

is valid.

IF B60 was 0, it would execute the FALSE, any other number would execute the TRUE part.