About Us

joeu2004

"Matt Ballantine" <Matt wrote:
=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(F65*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 -----

"Matt Ballantine" <Matt wrote in
message ...
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?

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Function argument correct result but answer 0 in cell?	Tonzie	Excel Worksheet Functions	3	March 17th 08 01:06 PM
Not obtaining the correct result	ZT	Excel Discussion (Misc queries)	1	May 3rd 06 07:19 PM
A correct IF function doesn't show the function's result in cell	Michael	Excel Discussion (Misc queries)	1	April 10th 06 09:10 AM
Function WEEKNUM does not show correct result for 15.2.2005	Edward	Excel Worksheet Functions	10	March 14th 05 11:13 AM
the result of nbval is not correct	Saf	Excel Discussion (Misc queries)	1	February 17th 05 12:54 PM

Menu

About Us