Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
rmb4253
 
Posts: n/a
Default Round up to next half number

I am trying to round up a number to what I recall from school days to be
"correct to the nearest half". In other words, if the cell value is between
3.01 and 3.49 I want to round it to 3.5. If the cell value is 3.5 to 3.99 I
want it rounded to 4.0

I have managed to round up and down to the nearest whole number but can't
figure out how to get it to the nearest half! Can anyone help, please?
RMB
  #2   Report Post  
Posted to microsoft.public.excel.newusers
Bernard Liengme
 
Posts: n/a
Default Round up to next half number

How's this =ROUNDUP(A1*2,0)/2
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"rmb4253" wrote in message
...
I am trying to round up a number to what I recall from school days to be
"correct to the nearest half". In other words, if the cell value is
between
3.01 and 3.49 I want to round it to 3.5. If the cell value is 3.5 to 3.99
I
want it rounded to 4.0

I have managed to round up and down to the nearest whole number but can't
figure out how to get it to the nearest half! Can anyone help, please?
RMB



  #3   Report Post  
Posted to microsoft.public.excel.newusers
daddylonglegs
 
Posts: n/a
Default Round up to next half number


You don't really want 3.5 to become 4 do you?

perhaps, if you only have positive numbers

=CEILING(A1,0.5)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=525473

  #4   Report Post  
Posted to microsoft.public.excel.newusers
rmb4253
 
Posts: n/a
Default Round up to next half number

Bernard, Thanks very much - that is marvellous!

RMB

"Bernard Liengme" wrote:

How's this =ROUNDUP(A1*2,0)/2
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"rmb4253" wrote in message
...
I am trying to round up a number to what I recall from school days to be
"correct to the nearest half". In other words, if the cell value is
between
3.01 and 3.49 I want to round it to 3.5. If the cell value is 3.5 to 3.99
I
want it rounded to 4.0

I have managed to round up and down to the nearest whole number but can't
figure out how to get it to the nearest half! Can anyone help, please?
RMB




  #5   Report Post  
Posted to microsoft.public.excel.newusers
Bernard Liengme
 
Posts: n/a
Default Round up to next half number

Happy to help
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"rmb4253" wrote in message
...
Bernard, Thanks very much - that is marvellous!

RMB

"Bernard Liengme" wrote:

How's this =ROUNDUP(A1*2,0)/2
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"rmb4253" wrote in message
...
I am trying to round up a number to what I recall from school days to be
"correct to the nearest half". In other words, if the cell value is
between
3.01 and 3.49 I want to round it to 3.5. If the cell value is 3.5 to
3.99
I
want it rounded to 4.0

I have managed to round up and down to the nearest whole number but
can't
figure out how to get it to the nearest half! Can anyone help, please?
RMB








  #6   Report Post  
Posted to microsoft.public.excel.newusers
rmb4253
 
Posts: n/a
Default Round up to next half number

daddylonglegs,

Yes I did want 3.5 to become 4 - but thanks for the formula anyway - I've
never used "Ceiling" before! I'll have to read up on it!

RMB

"daddylonglegs" wrote:


You don't really want 3.5 to become 4 do you?

perhaps, if you only have positive numbers

=CEILING(A1,0.5)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=525473


  #7   Report Post  
Posted to microsoft.public.excel.newusers
daddylonglegs
 
Posts: n/a
Default Round up to next half number


For positive numbers

=ROUNDUP(A1*2,0)/2

does the same as

=CEILING(A1,0.5)

neither of them will return 4 when A1 is equal to 3.5, what result do
you want when A1 is 3?


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=525473

  #8   Report Post  
Posted to microsoft.public.excel.newusers
rmb4253
 
Posts: n/a
Default Round up to next half number

Daddylonglegs,

Thanks for that! Actually you were right when you said I wouldn't want 3.5
rounded up so what I need is if the result is 3, then I want it to stay as 3.
Result of 3.01 to 3.5 to be 3.5 and 3.51 to 4 to be 4.

Your suggestion and that of Bernard's both do that so thanks again!

RMB

"daddylonglegs" wrote:


For positive numbers

=ROUNDUP(A1*2,0)/2

does the same as

=CEILING(A1,0.5)

neither of them will return 4 when A1 is equal to 3.5, what result do
you want when A1 is 3?


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=525473


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
Preceding a number by zeros, that is still a number Michele Excel Worksheet Functions 1 September 14th 05 01:06 PM
Number of labels on X-axis one more than number of values on Y-axi Gudrun Charts and Charting in Excel 5 August 26th 05 01:55 PM
Count Number of Characters in a cell? AHJuncti Excel Discussion (Misc queries) 2 June 16th 05 07:39 PM
Need number of Saturdays and number of Sundays between 2 dates Class316 Excel Worksheet Functions 1 June 10th 05 02:47 AM
How to format a number in Indian style in Excel? Victor_alb Excel Discussion (Misc queries) 2 December 21st 04 05:21 AM


All times are GMT +1. The time now is 02:42 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"