Rounding


M

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
 
Ad

Advertisements

D

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

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
 
M

Martina

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

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

Advertisements

Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top