I am having rounding errors in Excel. When I use a formula to make a calculation, the resulting numbers will not round correctly when a 5 is the third decimal place. It always rounds down, instead of up. I can enter the same number in another cell with no formula and it rounds correctly. This results in very inconsistent calculations. Any ideas or fixes? Thank you
What do you get if you format the number to show more decimal places? I expect you'll get something like x.xx47 which would round, to three places to x.xx5, but would still round down going to 2 places.
Thanks for your reply.
You are correct, I get values very close to that when moving it out to 4 decimal places. So i see how that would actually round down. But how can I obtain calculations that "work out"?
For instance, for presentation of my thesis data I have to have all my numbers round to 2 decimal places. I have two sets of raw numbers that have 3 decimal places, such as .516 and .445. Excel will round each number to .52 and .44 respectively and I want to use these numbers in the calculations. However, I have an equation set up that subtracts the first number from the second and the result is also rounded to 2 decimal places. This result is .07 when I would like it to be .08.
So I guess my question is...how do I get a formula to use the "rounded" numbers in its calculations rather than the originals that are several decimal places long?
Any ideas?
Last edited by Jbagger; 03-27-2007 at 01:19 AM.
It looks like you are confusing ROUND with displayed values.
If you include ROUND in the formula, Excel will round the number to the precision you need. If you just change the format, then the original number will still be there, it just 'appears' as a rounded number.
Check out the MOD function too
Ed
Hi,Originally Posted by Jbagger
perhaps a solution that I would not normally recommend (for obvious reasons) will assist you. In Tools, Options, Calculation is an option 'Precision as Displayed' which may help, but be careful to turn this off for other Worksheets.
hth
---
Si fractum non sit, noli id reficere.
I noticed that when you change to this option Excel tell you the changes are permanent. What does this do exactly?
You are correct, I am confusing two. It looks like I need to implement the ROUND function in several cells across numerous spreadsheets.
Is there a way I can implement the ROUND function to all cells in a spreadsheet without manually typing it before each equation?
Hi,
You might be able to do it with find and replace across the selected cells if there is a specific and unique format that that you can identify - but make a backup incase it finds more than you want!
If the same cells with the same formula in different sheets need changing, then you can group the sheets and then amend the formula just once.
Ed
Alrighty! Thanks for all of your guy's help! Much appreciated!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks