Rounding

[Martina]

Hi,
You do not realise my trepidation in asking this question for fear it is
supremely basic. Here goes...

If I ask Excel to ROUND(A1,0) , and 74.45 is in cell A1, why does it not
round to 75.0?

I have a grade that only kicks in at 75%(B grade). A student who gets
74.45% will not be awarded this grade but the one below. The cell is
formatted to show as 74.5, and it does not show up as a B but a C.

74.45, 74.46, 74.47, 74.48, 74.49 will all return 74 after ROUND(A1,0).
Intuitively I understand that the nearest whole number to 74.45 is 74, but
if you round 74.45 to 74.5 then it becomes 75 ... doesn't it :|

Please help
Martina

 

[David Biddulph]

Yes, it is supremely basic, as you expected.
The reason that 74.45 rounds to 74 is that it is nearer to 74 than to 75.

The formatting to show only one decimal place doesn't affect the number
stored (unless you select "precision as displayed" in your calculation
options).

If you particularly want to round to one place and then round again to zero
places, then you could use =ROUND(ROUND(A1,1),0) , but it would be a pretty
perverse thing to do.

 

[MartinW]

Hi Martina,

As David says your rules of rounding are a bit perverse.
To avoid arriving at that place, do your rounding at the start
of your calculations so that you don't arrive at awkward
results!

Following your logic I could make a case that a result of
52.26% eventually rounds to 100% and thus constitutes an A.

Please don't be offended by my remarks Martina, but the
precision you use has to be weighed up against the result you
are trying to achieve.

Sincerely
Martin

 

[Martina]

Thanks everyone. I appreciate your comments. I can see the error of my ways.
regards
Martina

 

[Stan Brown]

Thu, 10 May 2007 23:51:00 -0700 from Martina

If I ask Excel to ROUND(A1,0) , and 74.45 is in cell A1, why does it not
round to 75.0?

Because rounding happens all at once, not digit by digit. That's not
an Excel quirk; it's correct procedure.

Look at it this way: is 74.45 closer to 75 or to 74?

74.45, 74.46, 74.47, 74.48, 74.49 will all return 74 after ROUND(A1,0).
Intuitively I understand that the nearest whole number to 74.45 is 74, but
if you round 74.45 to 74.5 then it becomes 75 ... doesn't it :|

It does, but that's incorrect procedure. Sorry, the only correct way
to round is to do all other calculations first and then round the
final answer in one step.

 

[noahteo99]