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 5
But when I have
Round((1.045-1)*100,0) excel rounds to 4

But the operation still gives 4.5, so why does excel round to 4 and not 5

How do I get the equation/ operation to round to 5
Please HELP !!!

message edited by SCSTAR


See More: Rounding 0.5 in excel

Report •


✔ Best Answer
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).

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#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-1

Now 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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

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 formula

Round ((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)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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=357

Then 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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
November 12, 2014 at 05:31:36
It's actually a formula that I'm executing on a huge database.

formula is

Round ((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.045

Now 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)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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 357

but 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 answer


Like 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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
November 12, 2014 at 08:51:02
Excel's floating point calculations is what it messing things up

To 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/78113

MIKE

http://www.skeptic.com/


Report •

#11
November 12, 2014 at 08:51:32
✔ Best Answer
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).

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question