# Solved Rounding 0.5 in excel

November 11, 2014 at 09:36:49
Specs: Windows 7
 Hello,When I input Round(4.5,0) it will round to 5But when I have Round((1.045-1)*100,0) excel rounds to 4But the operation still gives 4.5, so why does excel round to 4 and not 5How do I get the equation/ operation to round to 5 Please HELP !!!message edited by SCSTAR

See More: Rounding 0.5 in excel

November 12, 2014 at 08:51:32
 I'm going to be tied up (not literally) for the rest of day, so I can't do any more work on this right now. Let me just throw this out for you to play with. If you come up with a solution that works consistently, let me know so that I don't keep working on it if I don't have to.Something like this technique, obviously replaced with your actual calculations, might help:We know that (1.045-1) introduces the floating point problem. However, 0.045+(1-1) does not. Therefore this formula will return FALSE even though it is technically TRUE.=(1.045-1) = 0.045+(1-1)So, how about using something like this...=IF((1.045-1)<>0.045+(1-1),"Modify the calculation","Don't modify the calculation")Basically what I am suggesting is that if you can extract some portion of your multiple calculation steps where the floating point issue does not impact the result and then use an IF function to compare that result to another portion, you might be able to fix the issue only when it occurs and not impact anything when it doesn't happen.I hope that helps (somewhat).

#1
November 11, 2014 at 10:59:29
 This is an interesting one. In your formula bar, highlight the portion of your formula that reads:1.045-1Now press F9.Why the formula as a whole is returning 4 makes perfect sense once you see that partial result, but why that partial result is happening is something that I will need to play with.EDIT:Oh, if you really want that specific formula to round to 5, use this:=ROUNDUP((1.045-1)*100,0)However, I don't know how that might impact other calculations that you are doing.message edited by DerbyDad03

Report •

#2
November 11, 2014 at 11:23:29
 Ahhh i see. It displays 0.044999999. So yes now i concur that the result is 4. But like you said why is it returning 0.04499999 and not 0.045, still remains a mystery. In the meantime i came up with a formulaRound ((Round (1.045,2)-1)*100,0) =5.It does the trick but i hope it doesn't mess up my other results. Thx

Report •

#3
November 11, 2014 at 12:41:00
 I'm curious...Are you actually using 0.045 in your formula or are you referencing a cell that contains 0.045?Seems like you are using a formula to do some simple math (1.045-1) and Excel's internal precision is messing you up.What don't I know?BTW...If you actually need to do the math in a formula, this shorter version works too:=ROUND(1.045*100-1*100,0)

Report •

Related Solutions

#4
November 11, 2014 at 12:58:39
 The 1.045 comes from another rounded formula. . Round (a/b,3)Where in this case a=373 & b=357Then i take the result of 1.045 and do the mathematical operations stated above to obtain a value of 5

Report •

#5
November 12, 2014 at 03:58:56
 So what is the actual formula that you are using? Are you manually entering the 1.045 into the formula that you started thus thread with once you have that result or is the ROUND(a/b,3) actually part of that formula?I'm trying to get a more complete picture of your process so I can see if there is a way to get the proper end result without the precision problem rearing its ugly head.

Report •

#6
November 12, 2014 at 05:31:36
 It's actually a formula that I'm executing on a huge database.formula isRound ((Round (a/b,3)-1)*100,0)Where a & b are calculated numbers but have been rounded to 0 decimals in previous steps

Report •

#7
November 12, 2014 at 07:27:59
 You still have not answered my question. The main issue is that you are not copying the formulas from your spreadsheet into your posts, you are typing invalid formulas into your posts and that is causing confusion.e.g. this formula will not work unless a & b are Named Ranges. Therefore, a & b must be something else and that is what I am asking about.From now on, please try to use actual formulas that will work if pasted into Excel. When I say "work" I mean that the syntax is correct, not that they necessarily return the value you are expecting. I can't test a formula when the syntax is not correct, so I have to rewrite them based on how I think they should be which may not be how you think they should be.I will try one more time....Using a & b in your explanations is a major part of the confusion. a & b can not be used in a formula unless they are Named Ranges, which I assume they are not. Therefore, this formula is invalid:=ROUND((ROUND (a/b,3)-1)*100,0)Here are my specific questions:How are the values for a & b getting into the actual formula that you are using in the cell? Are a & b numbers that you are entering manually once the other formula has calculated them are or you referencing the cell that contains the result of the other formula?For example, using A1 and B1 as example cell references...A1 contains =ROUND(373/357,3) therefore A1 displays 1.045Now that you have 1.045 in A1, which of these 2 formulas are you using in B1:=ROUND((1.045-1)*100,0)=ROUND((A1-1)*100,0)

Report •

#8
November 12, 2014 at 07:50:33
 Sorry I misuderstood your question.It's just that I have many steps/cells that calculate the 373 and 357but in general it's as follows=ROUND((ROUND(H361/(SUM(R110:X110)+SUM(R113:U114)+SUM(R118:X141)),3)-1)*100,0)where H361 = 373 (which is your a)SUM(R110:X110)+SUM(R113:U114)+SUM(R118:X141) = 357 (which is your b)All is done in one step, I even split it up in many steps and still comes up with the same answerLike I mentioned I have about 10 calculation leading up to these figures and I don't see the relavance of all the calculations prior to this, b/c if I type in excel =1.045 - 1, and I hover over it by clicking F9 I get 0.449999999 .. So that's why I don't see the relevance of how figures a & b are derived.I tried both your approaches:1) =ROUND((1.045-1)*100,0)2) =ROUND((A1-1)*100,0)And they both drive the same result i.e. 4

Report •

#9
November 12, 2014 at 08:24:27
 re: So that's why I don't see the relevance of how figures a & b are derived.The relevance was simply that I did not know how they were derived therefore I was not working with all of the information available. As I said earlier, I can't test... Round ((Round (a/b,3)-1)*100,0) ...to see what can be changed to end up with a consistent, accurate result, but I can test: =ROUND((ROUND(H361/(SUM(R110:X110)+SUM(R113:U114)+SUM(R118:X141)),3)-1)*100,0)I don't know if I will come up with a solution, but at least I now know what I am working with.The bottom line is that the internal precision of Excel's floating point calculations is what it messing things up and there may not be a simple, clean solution.

Report •

#10
November 12, 2014 at 08:51:02
 Excel's floating point calculations is what it messing things upTo see very clear example, enter this formula in a blank cell:=1*((0.5-0.4)-0.1)Is the answer 0 ???You would think so, but........For more info see here:https://support.microsoft.com/kb/78113MIKEhttp://www.skeptic.com/

Report •

#11
November 12, 2014 at 08:51:32