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?

 

[chicagocomputerclasses]

Do you have circular references in any of these cells?

 

[Jonmo1]

I have automatic calculations enabled, as well as iterative calculations (up to 100).

Having intentional circular references can cause strange issues.
It really is best to avoid them, and come up with an alternate solution for whatever task that seems to require them.

You never answered my questions?

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)

?

 

[Aladin Akyurek]

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

Care to post that number?

And in case, run the following:

=SIGN(sheetA!D7)

 

[Joe4]

I have merged your two threads together.

In the future, please do not post the same question multiple times. All clarifications, directly-related follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #12 here: Forum Rules).

 

[excellenfant]

chicagocomputerclasses, these cells don't contain circular references, though the model does.

Aladin, didn't know you needed the number. =Sheet returns $2,492,246, the same value produced by the formula in D7. Sign returns 1.

 

[Jonmo1]

=Sheet returns $2,492,246, the same value produced by the formula in D7. Sign returns 1.

Well, that is NOT a negative number, so your IF('sheetA'!D7<0 is FALSE.

 

[Aladin Akyurek]

chicagocomputerclasses, these cells don't contain circular references, though the model does.

Aladin, didn't know you needed the number. =Sheet returns $2,492,246, the same value produced by the formula in D7. Sign returns 1.

If that is the case, D7 is not less than 0, consequently you should get 0 as result given the formula you have.

 

[excellenfant]

Sorry, I gave that number as an example as I have continued playing around with the model, trying to get it to work. When the number is negative, the if formula does not read it. For example, if I manipulate D7 to be -$1,000,000, the if formula still doesn't read it. And, in that instance, =Sheet returns 2, even though the sheet containing D7 is the first sheet in the book.

 

[Jonmo1]

For example, if I manipulate D7 to be -$1,000,000

OK, no longer speaking in 'Examples'.
In Reality, when D7 is in THAT state containing -$1,000,000
What does this return
=ISNUMBER('SheetA'!D7)

?

 

[excellenfant]

Jonmo1, isnumber returns TRUE.