I cannot understand why I get different results in the last decimal place dependent on how the formula is entered.
I format a cell (say D1) as currency with 2 decimal places.
If I enter this in to D1
=(7208-411)*3.552/100
I get the correct answer 241.43
If however I enter the figures in to cells eg:
A1 = 7208
B1 = 411
C1 = 3.552
and then enter in D1
=(A1-B1)*C1/100
I get the answer 241.42
Can anyone explain why this happens please?
i cant duplicate the error.
how are A1, B1, C1 formatted?
Regards
Sean
Please add to my reputation if you think i helped (click on the star below the post)
Mark threads as "Solved" if you have your answer (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [code]
Please supply a workbook containing example Data: It makes its easier to answer your problem & saves time!
Peter, welcome to the forum.
Like Sean, I can't duplicate the error either - please see attached spreadsheet which shows that either way of calculating results in an answer of 241.429.
Brendan.
__________________________________________________________________________________________________
Things to consider:
1) You can thank any poster by clicking the * at the left of a helpful post.
2) You can help to keep the forum tidy by marking your thread as "Solved", if it has been answered to your satisfaction.
3) Help us to help you, by uploading a sample workbook, showing the type of data you're dealing with, and clearly indicating what the results should be.
Regardless of formatting, every mathamatical opperation creates errors
The first example is probably the most accurate.
In the second excel is acting on each variable more than once so compounding the errors.
If you set your number to display multiple decimal points such as 8 you will understand what is happening more clearly
????? How is that? Do you mean every mathematical operation has the potential for errors, due to rounding/inaccurate data entry, etc?Originally Posted by mehmetcik
No. Every mathematical operation in any computer processor (this is not restricted to Excel) has the potential for different results depending on how the data on which it is based has been created.. But this is not due to inaccurate data rather the limit on any number that can be stored in a given size of bit operation.
It's in fact a well know subject and much commented on and misunderstood. There's a large article here if you're interested.
http://support.microsoft.com/kb/78113
Suffice to say that if it's likely you may have numbers that could have decimals at the very extreme decimal places then you are better advised to wrap any formula that uses them in an =ROUND() function, and round off to a decimal place well before the back end of a decimal expansion.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Thanks, Richard - considerations regarding data-types aside, you'll no doubt appreciate that "Every mathematical operation in any computer processor (this is not restricted to Excel) has the potential for different results " is a fundamentally different statement to "every mathamatical opperation creates errors".
Cheers also for the link.
....indeed. But it wasn't me wot said that guv.
I realise that, guv- I probably should have made it clearer in my post that the two quotes were from different posters, to avoid any potential confusion in that respect. I'll know better the next time!
Thanks for all the replies. I too found I could not duplicate the error when I set up a new spread sheet with the values I had originally provided in my query. I found that cell A1 (7208) which was formatted as number with 0 decimal places was the result of a calculation (11.1922266666666 * 644) which is 7207.7939733.
I have now altered A1 to =ROUND(11.19322266667*644,0) and this now produces the 241.43 result I expected.
It nicely illustrates the point made by Richard.
peter_h
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks