Round up to next half number

[rmb4253]

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

[Bernard Liengme]

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

"rmb4253" <[email protected]> wrote in message
news:[email protected]...
>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

[daddylonglegs]

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

perhaps, if you only have positive numbers

=CEILING(A1,0.5)

[rmb4253]

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" <[email protected]> wrote in message
> news:[email protected]...
> >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
>
>
>

[Bernard Liengme]

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

"rmb4253" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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
>>
>>
>>

[rmb4253]

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

[daddylonglegs]

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?

[rmb4253]

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