Round Function yields incorrect result

[Helen28]

Hi there,

I am using the Round Function to round some computed percentage values to 0 decimals.

The values are:

[TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl63, width: 83"]48,4848485
[TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl65, width: 83"]27,2727273
[TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl65, width: 83"]24,2424242

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
So 100% all together.

For the first number the Round Function, i.e. =Round(48,4848485,0), rounds down to 48!! This is incorrect, as it should round up to 49! For the remaining two it yields correct number, i.e. 27 and 24.

Does anyone have an idea why it wouldn't round correctly?

Thanks,
Helen

 

[Peter_SSs]

=Round(48,4848485,0), rounds down to 48!! This is incorrect, as it should round up to 49!

As the fraction part is less than half, that rounding seems perfectly reasonable to me. What is your reasoning for expecting that to round up from below halfway?

 

[Helen28]

Hi Peter,
thanks for your reply!
I would round up to 49, since:

48,48 rounds up to 48,5 and
48,5 rounds up to 49

Does that make sense?
I need all rounded values to add up to 100% as well, which is not the case with Excel rounding down to 48.

Thanks,
Helen

 

[Peter_SSs]

I would round up to 49, since:

48,48 rounds up to 48,5 and
48,5 rounds up to 49

Does that make sense?

Not any mathematical sense at all.

I need all rounded values to add up to 100% as well,

That is a requirement that I understand, but what is the logic for deciding which value(s) to possibly round mathematically incorrectly to achieve that result?

As another example, what if the original calculated values were as follows?
They add up to 100%, but when rounded mathematically, they would all round to 33%.
How would you round each one and why?

33.334%
33.332%
33.334%

 

[Helen28]

Hi Peter,

good point!
I should have probably revisited some basic maths before posting my question. Somehow I assumed you don't only look at the next digit but start at the last and round each digit separately .
I guess I will round to two decimal places, as that seems to get around the issue of the numbers not adding up to 100%.

Thanks for your input!

 

[Peter_SSs]

I guess I will round to two decimal places, as that seems to get around the issue of the numbers not adding up to 100%.

Will it? With my previous example (that adds up to 100%) if you round each to 2 decimal places you get
33.33
33.33
33.33
which adds to 99.99