#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 25
Default Rounding

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



  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default Rounding

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.
--
David Biddulph

"Martina" wrote in message
...
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





  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 860
Default Rounding

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


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 25
Default Rounding

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

"MartinW" wrote:

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



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 524
Default Rounding

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.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Rounding


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Rounding Bill Ridgeway New Users to Excel 2 November 21st 06 12:32 PM
Rounding elusiverunner Excel Discussion (Misc queries) 5 November 15th 06 02:25 AM
Rounding Up Frustrated Excel Worksheet Functions 2 October 26th 06 04:54 PM
Rounding Off!? Neo1 Excel Worksheet Functions 10 March 10th 06 09:11 PM
ROUNDING susan slate Excel Worksheet Functions 5 September 29th 05 06:54 PM


All times are GMT +1. The time now is 02:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"