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(E5148.5-E4,"Error", " ")
formula in F9: =IF(E4+E5148.5,"Error", " ")

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

€žBob€� ezt Ã*rta:

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(E5148.5-E4,"Error", " ")
formula in F9: =IF(E4+E5148.5,"Error", " ")

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

"Bob" wrote in message
...
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(E5148.5-E4,"Error", " ")
formula in F9: =IF(E4+E5148.5,"Error", " ")

On Oct 7, 10:04*am, Stefi wrote:
For me both formula gave "Error". Maybe you should check your typing!
Regards,
Stefi

„Bob” ezt írta:



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(E5148.5-E4,"Error", " ")
* *formula in F9: =IF(E4+E5148.5,"Error", " ")- Hide quoted text -

- Show quoted text -

Note that both E4 and E5 have 2 decimal places.

On Oct 7, 10:06*am, "Niek Otten" wrote:
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

"Bob" wrote in message

...



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(E5148.5-E4,"Error", " ")
formula in F9: =IF(E4+E5148.5,"Error", " ")- Hide quoted text -

- 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