Rounding to the nearest whole or .5

[Sir Babydum GBE]

Hi I need a formula in B1 that will look at cell A1, divide it by 60 and the round up to the nearest whole or half.

Obviously I know how to do the dividing :) but it's forcing the roundup to say the half I have difficulty with.

Thanks

[sheeeng]

try
Round(total [,numdecimalplaces]);

[Marcster]

Hi Babydum,

Does this work:


=ROUNDUP(A1/60,2)

Marcster.

[sheeeng]

Hi Babydum,

Does this work:

=ROUNDUP(A1/60,2)

Marcster.

That's new to me! Thx! :thumb

[Marcster]

That's new to me! Thx! :thumb

There's also
=ROUNDDOWN(number,num_digits)

Marcster.

[Sir Babydum GBE]

Thanks guys.

it doesn't work because it doesn't round to .5.

I know how to use roundup in the way you've demonstrated - but the question really relates to how to force the result to a whole number or exactly half (i.e. 6.7 won't do - it'll have to be 7; and 2.2 won't do - it'll have to be 2.5)

Any ideas?

[MWE]

brute force, but try this:


=INT(A1/60)+IF(A1/60-INT(A1/60)=0,0,IF(A1/60-INT(A1/60)>0.5,1,0.5))

[TonyJollans]

What you need for this is the CEILING Function ...


=CEILING(A1/60,0.5)

[johnske]

as a VBA routine:[


Option Explicit

Sub NearestHalf()
Dim N As Double, Fraction As Double
N = 22.6
If 2 * (N - Int(N)) \ 1 = 0 Then
Fraction = 0
ElseIf 2 * (N - Int(N)) \ 1 = 1 Then
Fraction = 0.5
ElseIf 2 * (N - Int(N)) \ 1 = 2 Then
Fraction = 1
End If
MsgBox Int(N) + Fraction
End Sub

[Sir Babydum GBE]

Thanks all.

Tony, your CEILING formula seems to be the simplest way to do the trick. And never used INT before MWE.

Johnske - code! wow, thanks, I wasn't expecting code, but then again I did post on a VBA site.

Sheeng and Marcster - sorry for not being clear in the first place!

You guys are great.

You all get one half price ticket to ask an ethereal question in my forum! :)

[MWE]

What you need for this is the CEILING Function ...


=CEILING(A1/60,0.5)
I have never encountered the CEILING function before. It is pretty neat. :thumb

I was happy to see (after a little testing) that the CEILING function and my pathetic attempt to solve this problem yielded the same results except for negative numbers. CEILING rounds "away" from zero, to if A1 = -63, CEILING(A1/60,-.5) yields -1.5. For a similar value of A1, my formula yields -1 (it rounds up consistently)

So, a better and more general solution might be


=CEILING(A1/60,0.5*SIGN(A1))

[Sir Babydum GBE]

Thanks MWE,

In my circumstances, all numbers are greater than zero. People input number of minutes something should take, and I needed the formula to turn that into hours, rounded up to the nearest half hour.

I would say that going the long way round something isn't pathetic - just longer.

Damo

[TonyJollans]

Just in case you didn't see it, the opposite of CEILING, which rounds towards zero is FLOOR.

[Zack Barresse]

Yes indeed, CEILING and FLOOR are the proper functions for this type of application. A note of caution when using one of the many rounding functions Excel has to offer: be sure you have the right function for the right application. There are some that look like they do the same thing, but not one of them are the same - not one.

[avkb03]

Here are 2 functions that work great.



Function RoundUpDown(varNumber, varDelta)
'Name: RoundDelta (Function)
'Purpose: round varNumber to varDelta, up or down
'Inputs: varNumber = number to round
' varDelta = rounding precision
' +varDelta = rounds UP
' -varDelta = rounds DOWN
'Example: RoundUpDown(5.12,+0.25) = 5.25
' RoundUpDown(5.12,-0.25) = 5.00
'Output: varNumber rounded UP/DOWN
Dim varTemp
varTemp = varNumber / varDelta
If Int(varTemp) = varTemp Then
RoundUpDown = varNumber
Else
RoundUpDown = Int( _
((varNumber + (2 * varDelta)) / varDelta) - 1) _
* varDelta
End If
End Function

Function RoundNear(varNumber, varDelta)
'Name: RoundNear (Function)
'Purpose: rounds varnumber to the nearest fraction equal
' varDelta
'Inputs: varNumber - number to round
' varDelta - the fraction used as measure of
' rounding
'Example: RoundNear(53,6) = 54
' RoundNear(1.16,0.25) = 1.25
' RoundNear(1.12,0.25) = 1.00
' RoundNear(1.125,0.25)= 1.25
'Output: varNumber rounded to nearest
' multiple of varDelta.
Dim varDec
Dim intX
Dim varX
varX = varNumber / varDelta
intX = Int(varX)
varDec = varX - intX
If varDec >= 0.5 Then
RoundNear = varDelta * (intX + 1)
Else
RoundNear = varDelta * intX
End If
End Function

[Sir Babydum GBE]

Okay, thanks guys :)

I guess I can mark this thread "Solved". In fact, if you had the right button, I'd mark it "Very Solved" :yes