IF Formula Returng a FALSE value

[Mike Loustalot]

I have created an IF formula that is not returning the
correct value into the cell. The value should be zero (0)
but instead it is returning a middle function. Here is
the formula.

=-IF(SUM($C$19*($D$14:K14))<=54677,($C$19*K14),0)

In this case the SUM Function equals 70752, which by my
calculations is less than or equal to 54677, which makes
this statement false, which should return 0. But the
value in the cell is 11088, which is represented by
($C$19*K14).

Any ideas????//

 

[Kevin H. Stecyk]

Mike Loustalot wrote...

I have created an IF formula that is not returning the
correct value into the cell. The value should be zero (0)
but instead it is returning a middle function. Here is
the formula.

=-IF(SUM($C$19*($D$14:K14))<=54677,($C$19*K14),0)

In this case the SUM Function equals 70752, which by my
calculations is less than or equal to 54677, which makes
this statement false, which should return 0. But the
value in the cell is 11088, which is represented by
($C$19*K14).

Mike,

With that formula in the formula bar, select "SUM($C$19*($D$14:K14))"
portion of the formula, and then press F9. It should evaluate that portion
of the formula.

Then you can check to see if the value XL sees is the same value you expect.

As an irrelevant aside, you can shorten your formula slightly to....

=-IF(SUM($C$19*($D$14:K14))<=54677,$C$19*K14,0)

I just removed one set of brackets.

Good luck.

Best regards,
Kevin

 

[Mike Loustalot]

-----Original Message-----
Mike Loustalot wrote...

Mike,

With that formula in the formula bar, select "SUM($C$19* ($D$14:K14))"
portion of the formula, and then press F9. It should evaluate that portion
of the formula.

Then you can check to see if the value XL sees is the same value you expect.

As an irrelevant aside, you can shorten your formula slightly to....

=-IF(SUM($C$19*($D$14:K14))<=54677,$C$19*K14,0)

I just removed one set of brackets.

Good luck.

Best regards,
Kevin

Thanks Kevin,

I have done the f9 to evaluate the formula and the number
is what I expect. Furthermore, when I click on the = sign
in the Formula Bar and the fx dialog box opens, it shows
the formula result as 0, which is what I want, but the
value in the cell is something different! Very puzzling.

 

[CLR]

Hi Mike...........

Perhaps C19 is actually a negative number...........that's the way it worked
out in my model........the formula worked fine with a positive number in C19
but returned (C19*K14) when I changed the sign in C19 to negative..........

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

-----Original Message-----
Hi Mike...........

Perhaps C19 is actually a negative

number...........that's the way it worked

out in my model........the formula worked fine with a positive number in C19
but returned (C19*K14) when I changed the sign in C19 to negative..........

Vaya con Dios,
Chuck, CABGx3

C19 is .5