IF formula won't read criterion cell

[excellenfant]

Hello,

I have an if formula, if('sheetA'!D7<0,abs('sheetA'!D7),0). The value in the criterion cell is substantially lower than 0. Nevertheless, the if formula doesn't return the absolute value. No error code shows. The formula does not show as text. Just in case, I checked the formats anyway.

This is part of a pro forma model for class with an automatic drawdown on a revolver. I have automatic calculations enabled, as well as iterative calculations (up to 100).

Any thoughts?

 

[Jonmo1]

Welcome to the board.

You said it doesn't return the absolute value.
What result DOES the formula give?

What does this return
=ISNUMBER('sheetA'!D7)

?

 

[JLGWhiz]

Is your Sheet name "sheetA" or just "A"?

 

[excellenfant]

Hello,

I have an if formula, if('sheetA'!D7<0,abs('sheetA'!D7),0). The value in the criterion cell is substantially lower than 0. Nevertheless, the if formula doesn't return the absolute value. No error code shows. The formula does not show as text. Just in case, I checked the formats anyway.

This is part of a pro forma model for class with an automatic drawdown on a revolver. I have automatic calculations enabled, as well as iterative calculations (up to 100).

Any thoughts?

 

[chicagocomputerclasses]

Is it possible the number in D7 cell is text that looks like a number?

 

[excellenfant]

I just double checked. D7 format is accounting, and the formula cell is in currency.

 

[Aladin Akyurek]

=IF(sheetA!D7<0,ABS(sheetA!D7),0)

should work.

What do you get with the following?

=ISNUMBER(sheetA!D7)

=sheetA!D7&""

 

[excellenfant]

Isnumber returns TRUE. The second test returns the number in cell D7.

 

[chicagocomputerclasses]

This might be a stupid question, but you do have an equal sign before the formula right?

=IF(SheetA!D7<0,ABS(SheetA!D7),0)

 

[excellenfant]

Yes, there is an = sign in front of the formula.

I didn't think about this until just now when I noticed a similar problem with a different If formula, in cell B5. Both D7 and the criterion cell for the formula in B5 themselves contain a formula, albeit simple ones similar to =(a1+a2).

Will an If formula not read a cell containing another formula?