[newbie] rounding anomaly

[Jon]

I've formatted a cell as a percentage column with a precision of 3 d.p.
However, when I work out the average, the answer is inaccurately.

So,

cell D80 =POWER((1 + D76/$D$82),$D$82)-1 and is formatted as a percentage
with 3 d.p.

Value = 12.683

cell D81 =(D79+(D78*D75))*D80

The answer comes up as 5073, whilst when working out cell D81 manually:

35000 + (0.10 * 50000)) * .012683 = 5073.20

Is this a bug?


MS Excel 2007

NOTE: It is noted when selecting the 'Excel' link within these discussion
groups, the user (me) is directed to a page where I have to select the
appropriate forum from a list of Microsoft products, rather than being
directed directly (excuse the pun) to the Excel discussion groups
(http://www.microsoft.com/office/com...?dg=microsoft.public.excel.misc&lang=en&cr=US)
(http://www.microsoft.com/office/community/en-us/FlyoutOverview.mspx#2)

 

[Sheeloo]

Formatting a cell to 3 decimals does not change the underlying value...You
can see the underlying value in the FORMULA bar or by increasing the decimal
places in the format.

So even when you see 12.683 the actual value might be something like
12.68350023457

The underlying value is used in calculations and not the displayed value
hence the difference...

 

[Jon]

I'm aware of that, but still I'm not sure why the answer was 'rounded up';
maybe my calculator is incorrect?

 

[Sheeloo]

For =(35000 + (0.1 * 50000)) * 0.012683
I get 507.32 with Excel as well as my calculator..

Regarding
This is the behavior I get in FIREFOX.. Internet Explorer works fine.
I use IE Tab addon for Firefox
https://addons.mozilla.org/en-US/firefox/addon/1419

It renders the page with IE engine within Firefox...

 

[Joe User]

Is this a bug?

Most likely your misunderstanding, not a bug. But....

cell D80 =POWER((1 + D76/$D$82),$D$82)-1 and
is formatted as a percentage with 3 d.p.
Value = 12.683

.....It is difficult to offer assistance without knowing what is in D76 and
D82 -- the exactly values, not the what you might see displayed. If D76 and
D82 are formulas, not constants that you entered, then at a minimum, format
D76, D82 and D80 as Scientific with 14 dp, and post the values that you see.

cell D81 =(D79+(D78*D75))*D80
The answer comes up as 5073, whilst when working
out cell D81 manually:
35000 + (0.10 * 50000)) * .012683 = 5073.20

(I assume you mean 0.12683, which is 12.683%.)

First, 35000, 0.10 and 50000 might not be the exactly values in the cells,
merely the displayed values.

Second, the displayed value in D80, 12.683%, might really be any value
between 12.6825% and about 12.683499...%. In another posting, you say you
understand that. But....

In fact, with 12.6825% in D80, I get 5073.000000 using the other constants
as you specified them. So I suspect that is the real value in D80. Format
with 4 or more dp to see this.

PS: I confirm that with 12.683% and the other constants as you specified
them, I get 5073.20, as you did.


----- original message ----