Round values

[gustaf111]

Hello, it is possible if you have a series of values round them up/down so the values get a fixed number ? In the case below I have used the ROUND function but the sum became 201, I would like the sum to be 200!

Thanks Gustaf

17,64705882 +
61,76470588 +
67,64705882 +
52,94117647 +
= 200

Use ROUND
18
62
68
53
= 201

 

[Welshbird]

Why don't you 'round' the answer rather than each part of the sum?

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[mintjulep]

Or better yet, use FLOOR()

 

[TonyJollans]

As this is the VBA forum, FLOOR isn't really relevant, but, in Excel, it will give the wrong result. Rounding the result, as Fee suggests, is the thing to do.


Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[SkipVought]

BTW,

The ROUND function does not CHANGE the valuse in each cell. It only changes the DISPLAY you what you see.

You see 18, but the VALUE is stil 17,64705882

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Deniall]

If you have a set of individual values and a known total, you cannot avoid the potential for an arithmetic discrepancy if you ROUND the individual values and also ROUND the total. The solution I tend to use under these circumstances is:
» ROUND the total;
» ROUND all individual values except the one with the largest magnitude;
» Calculate the required rounded value for the one remaining individual number by subtraction of the n-1 rounded amounts from the rounded total.

BTW, Skip. I think you got it the wrong way round (pun intended) in your 16Sep11@08:42 post. It is the formatting operation that changes the presentation without changing the underlying value.