IF function inequality is not calculating correct result

[Matt Ballantine]

I recently copied a IF formula to a new sheet and am finding that the
inequality contained in the statement is not providing the correct results.
Here is the formula:

=IF((F6>(I6*5)), ((ABS(G6-F6))/((F6+G6)/2)), "NA")

The results are showing the formula calculation, even when the inequality is
not true and the "NA" should be showing. For example, where F6 is less than
I6*5, the cell contents do not show "NA" but the calculated result. The
formula is working fine in other places in the workbook, but just not in this
one section.

Any help?

 

[Sean Timmons]

If you could show the values you have in F6 and I6? Since the formula works
elsewhere, somethign must not be accurate in the section you're referencing.

 

[Sean Timmons]

If you could show the values you have in F6 and I6? Since the formula works
elsewhere, somethign must not be accurate in the section you're referencing.

 

[Bassman62]

Matt,
The formula as stated tests if F6 is Greater Than I6*5 and works for me.
However, the term "inequality" would suggest the "<>" 'not equal to'
operator. If you really need to test that F6 is Not Equal to I6*5, change
the ">" to" <>".
Hope this helps.

Dave

 

[Bassman62]

Matt,
The formula as stated tests if F6 is Greater Than I6*5 and works for me.
However, the term "inequality" would suggest the "<>" 'not equal to'
operator. If you really need to test that F6 is Not Equal to I6*5, change
the ">" to" <>".
Hope this helps.

Dave

 

[JoeU2004]

=IF((F6>(I6*5)), ((ABS(G6-F6))/((F6+G6)/2)), "NA")
The results are showing the formula calculation, even when the inequality
is
not true and the "NA" should be showing. For example, where F6 is less
than
I6*5, the cell contents do not show "NA" but the calculated result.

What makes you think F6 is truly less than 5*I6? My guess: you are being
misled by the displayed value due to formatting. That is, the displayed
value is not the true underlying value.

In most cases, you can confirm this by formatting the each of the cells F6
and I6 with Number and some number of decimal places so that 15 digits
appear, not counting leading zeros. You will probably see that "what you
see is __not__ what you get".

(But note: The difference might not be visible even when you display 15
significant digits.)

As for the solution, that depends. Generally, using the ROUND function in
one place or another (or everywhere) is the right solution. At a minimum,
for example:

=if(round(F6,2)>5*round(I6,2), abs(G6-F6)/((F6+G6)/2), "NA")

But that begs the question: do you also want to use ROUND in the expression
to the left of "NA"? Probably.

More likely, the better solution is to use ROUND prolifically in all
non-constant formulas, instead of relying on cell formatting to merely
affect the precision displayed. That is, each of F6, I6 and G6 should be of
the form (or an equivalent):

=ROUND(formula,2)

And your IF formula becomes:

=if(F6>5*I6, round(abs(G6-F6)/((F6+G6)/2),2), "NA")

PS: Another alternative is to use the "Precision as displayed" option under
Tools > Options > Calculation. I do to recommend that because it can lead
to unexpected surprises, some of which are irreversible.


----- original message -----

 

[JoeU2004]

=IF((F6>(I6*5)), ((ABS(G6-F6))/((F6+G6)/2)), "NA")
The results are showing the formula calculation, even when the inequality
is
not true and the "NA" should be showing. For example, where F6 is less
than
I6*5, the cell contents do not show "NA" but the calculated result.

What makes you think F6 is truly less than 5*I6? My guess: you are being
misled by the displayed value due to formatting. That is, the displayed
value is not the true underlying value.

In most cases, you can confirm this by formatting the each of the cells F6
and I6 with Number and some number of decimal places so that 15 digits
appear, not counting leading zeros. You will probably see that "what you
see is __not__ what you get".

(But note: The difference might not be visible even when you display 15
significant digits.)

As for the solution, that depends. Generally, using the ROUND function in
one place or another (or everywhere) is the right solution. At a minimum,
for example:

=if(round(F6,2)>5*round(I6,2), abs(G6-F6)/((F6+G6)/2), "NA")

But that begs the question: do you also want to use ROUND in the expression
to the left of "NA"? Probably.

More likely, the better solution is to use ROUND prolifically in all
non-constant formulas, instead of relying on cell formatting to merely
affect the precision displayed. That is, each of F6, I6 and G6 should be of
the form (or an equivalent):

=ROUND(formula,2)

And your IF formula becomes:

=if(F6>5*I6, round(abs(G6-F6)/((F6+G6)/2),2), "NA")

PS: Another alternative is to use the "Precision as displayed" option under
Tools > Options > Calculation. I do to recommend that because it can lead
to unexpected surprises, some of which are irreversible.


----- original message -----