problem in Excell 2003


B

Bob

I created a spreadsheet using Excel 2003.
Error is displayed in cell F7 mistakenly, and it is not displayed in
cell F9.
The formulas used in those two cells (F7 vs F9) are mathematically
equivalent.
Can anyone figure out why it happens?

Thanks.

column B column C column D column E column F
Group Actual Purity Adjusted Total Adjusted
Weight Weight Weight
row 4 1 140.60 99.90% 140.46 140.460000
row 5 2 8.05 99.90% 8.04 148.500000

row 7 Check 1 (Display Error if E5 > 148.5 - E4) Error

row 9 Check 2 (Display Error if E4 + E5 > 148.5)

Note: "Precision as Displayed" in
Tools/Options/Calculation is checked.
formula in E4: =C4*D4
formula in E5: =C5*D5
formula in F4: =E4
formula in F5: =E4+E5
formula in F7: =IF(E5>148.5-E4,"Error", " ")
formula in F9: =IF(E4+E5>148.5,"Error", " ")
 
Ad

Advertisements

S

Stefi

For me both formula gave "Error". Maybe you should check your typing!
Regards,
Stefi

„Bob†ezt írta:
 
N

Niek Otten

Since you use "precision as displayed" not just the "mathematical" results
count, but also the formatting.
If I reproduce you case without that setting and with no explicit
formatting, both formulas generate "Error".
 
B

Bob

For me both formula gave "Error". Maybe you should check your typing!
Regards,
Stefi

„Bob” ezt írta:







- Show quoted text -

Note that both E4 and E5 have 2 decimal places.
 
Ad

Advertisements

B

Bob

Since you use "precision as displayed" not just the "mathematical" results
count, but also the formatting.
If I reproduce you case without that setting and with no explicit
formatting, both formulas generate "Error".

--
Kind regards,

Niek Otten
Microsoft MVP - Excel









- Show quoted text -
Niek,

Thank you for your quick response.

E4 and E5 must have 2 decimal places.

Because 8.04 (E5) = 148.50 - 140.46 (E4) and not larger than 148.50 -
140.46 (E4), Error should not be displayed in F7 or F9.

I de-seleced Precision as Displayed, and used E4 = text(C4*D4,0.00) &
E5 = text(C5*D5, 0.00) to specify two decimal places for E4 and E5.
The results in F7 and F9 are still different. F7 displayed Error and
F9 did not.

It seems to be working if using text function for E4 and E5, and using
the formula in F9. But I can't explain why.

Is there any other way to resolve this issue?

Regards,

Bob
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top