Round Function yields incorrect result

Helen28

New Member
Joined
Jul 3, 2018
Messages
5
Hi there,

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

The values are:

48,4848485
27,2727273
24,2424242


<tbody>
</tbody>

<tbody>
</tbody>

<tbody>
</tbody>
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,269
Office Version
365
Platform
Windows
=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

New Member
Joined
Jul 3, 2018
Messages
5
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,269
Office Version
365
Platform
Windows
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

New Member
Joined
Jul 3, 2018
Messages
5
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 :eek:.
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,269
Office Version
365
Platform
Windows
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
 

Forum statistics

Threads
1,082,286
Messages
5,364,292
Members
400,787
Latest member
bs04c

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top