#1
|
|||
|
|||
Rounding values inconsistent
Hope this is easy. I have a series of calculations and appear to be having trouble with rounding. I'll do my best to explain. Cells C2 through C7 are multiplied by a different 'static' number and rounded to 2 decimal places. I then add C2 through C7 and put the sum in C9. With frequency, the total showing in C9 is NOT equal to the values shown in C2 >C7. If the numbers don't match, the difference is always 0.01. I suspect this is due to the rounding. How do I get the value in C9 to give an accurate value? Or, is the C9 value correct but one of the values in C2>C7 wrong? |
#2
|
|||
|
|||
I think the problem is that the cells in C are not really rounded, only their display is limited to some number of decimals. If you have a cell that has a value of 3.14159265358979, when displayed with just two decimals, it appears to be 3.14 though the actual value stored is 0.00159265358979 greater.
This is a disconnect between the spreadsheet world and the world of science where it is necessary to mind significant figures of precision in order to calculate results that most accurately reflect that precision. It also presents problems such as what you are facing though few people worry about a 0.01 difference. It can be confusing if you are presenting numbers that should add to a known figure, say 100.00, and the total comes up as 99.99. You might want to add another column that rounds the numbers in C to a specified number of digits and then add those. I'm pretty sure that will do the trick. =round(c2,2) |
#3
|
|||
|
|||
Thanks for the reply. Makes sense.
I'm a little confused on the solution. As I understand this, I put =round(c2,2) in, say, D2 =round(c2,3) in, say, D3, etc is that correct? I suppose D2 'sees' the value in C2 and changes it automatically? |
#4
|
||||
|
||||
I agree with your diagnosis, required—it has to be a rounding issue—and with gebobs' suggestion. But I'll expand on it a bit in case there's still confusion: As a matter of picky terminology, I would say that the value in C9 is accurate, just not precise.
Take an example: Code:
3.14159265359 2.71828182846 2.99792458000 1.41421356237 Now tell Excel to display them to just one decimal point, like this: Code:
3.1 2.7 3.0 1.4 What do you do about it? Well, it depends on your needs. It would probably be more accurate for many purposes to leave it as it is, giving 10.3 as the sum rather than 10.2. But if you need to for some reason, you can round the individual numbers to their truncated form, and the sum of those values (rather than the real ones) will come out to 10.2 as you expected. Extra credit for identifying the sample numbers above. |
#5
|
|||
|
|||
Quote:
e c (without the *10^8) sqrt(2) What do I win?!! |
#6
|
|||
|
|||
In a nutshell, yes!
|
#7
|
||||
|
||||
My undying respect .
|
#8
|
|||
|
|||
Thanks for the explanation.
In my case, the values are $0.00 so exact is a necessity. I'll try "ROUND" and see if it solves my problem. I would expect I can avoid teh middle man and have the value in C2 calculated as =ROUND(R7*J2+J5,2). Correct? As for the values, #1 is Pi, #4 is 2squared, number 2 . . . I'm not sure. I think it's 'e' but that's an educated guess. @3? No idea. Doesn't look familiar. |
#9
|
|||
|
|||
Nice! That and $4.29 will get me an overpriced Starbuck's coffee.
|
#10
|
||||
|
||||
Sure, you can. All it cares is that it has some kind of numeric value to work with. Sometimes in my anxiety to boil things down to as few steps as possible I combine so many things into one that it gets much harder to understand; in such a case if I overdo it, it's sometimes better to break it up into pieces. But you're not near that threshold here..
|
#11
|
|||
|
|||
Very good. Tweaked it a little, but worked.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Inconsistent Formatting | YEG_V | Word Tables | 3 | 05-08-2013 03:06 PM |
Custom formatting code - rounding problem | venkys4u | Excel | 1 | 08-14-2012 07:45 PM |
Standard Dev. Rounding - Excel | judicial85 | Excel | 1 | 08-28-2011 05:41 PM |
Inconsistent Margins | judyfogarty | Word | 0 | 08-01-2010 02:54 AM |
Federal financial rounding? | markg2 | Excel | 6 | 05-26-2010 09:54 AM |