Try this: Round Doubles with custom floor and ceiling to rounding

[ClimoC]

Anyone come across this before?

Essentially, trying to round like this:

04:01:00 to 04:00:00
04:02:00 to 04:00:00
04:03:00 to 04:00:00
04:04:00 to 04:05:00

04:06:00 to 04:05:00
04:07:00 to 04:05:00
04:08:00 to 04:05:00
04:09:00 to 04:10:00

etc

so kind of like normal rounding to 5 min intervals, which I've sorted, but I need to move the floor/ceiling so that 4's and 9's go up, and 1's,2's,3's go to 0 and 6's 7's and 8's go down to 5

Bit of monday-brainness, and in the middle of an 800 line script (not including functions) and wanted an elegant function solution.

Can't use mod obviously...

 

[Andrew Poulsom]

How about?

=CEILING((A1*24*60)-3,5)/24/60

 

[ClimoC]

How about?

=CEILING((A1*24*60)-3,5)/24/60

As it'll be a vb Function, I've swapped it around to:

Public Function FiveMinRound(R As Double) As Double

           FiveMinRound = Ceiling((R * 24 * 60) - 0.00208333333, 0.00347222222222) / 24 / 60
    
End Function


Public Function Ceiling(ByVal X As Double, Optional ByVal Factor As Double = 1) As Double

    Ceiling = (Int(X / Factor) - (X / Factor - Int(X / Factor) > 0)) * Factor
    
End Function

Where R is the time value that is being rounded,
0.00208333333 = 00:03:00
&
0.00347222222222 = 00:05:00


I'm assuming that was what you were getting at Andrew?

Didn't quite work for me. Am going to have a play in the Intermediate window first with what you've kindly pointed me towards, and see if I can get a working solution.

In the mean time, if anyone else comes up with something, would be cool to share!

C

 

[Andrew Poulsom]

More like:

Public Function FiveMinRound(R As Double) As Double
    FiveMinRound = WorksheetFunction.Ceiling((R * 24 * 60) - 3, 5) / 24 / 60
End Function

 

[ClimoC]

More like:

Public Function FiveMinRound(R As Double) As Double
    FiveMinRound = WorksheetFunction.Ceiling((R * 24 * 60) - 3, 5) / 24 / 60
End Function

Tres Magnifique Monsier Poulsom!

Though changed it to

Public Function FiveMinRound(R As Double) As Double
    FiveMinRound = WorksheetFunction.Ceiling((R * 24 * 60) - 4, 5) / 24 / 60
End Function

To make 4 -> 5 and 3:59 -> 0

 

[ClimoC]

Final question actually - Would using 'worksheetfunction' on 2 recordsets for approx 100,000 to 1,000,000 calculations not be a lot slower than a VB exclusive UDF?

If I'm right, well I'm sure I can find the worksheet function as a UDF somewhere. The two I've picked up a while ago:

Public Function Ceiling(ByVal X As Double, Optional ByVal Factor As Double = 1) As Double

    Ceiling = (Int(X / Factor) - (X / Factor - Int(X / Factor) > 0)) * Factor
    
End Function

Public Function Floor(ByVal X As Double, Optional ByVal Factor As Double = 1) As Double
    ' X is the value you want to round
    ' is the multiple to which you want to round
    Floor = Int(X / Factor) * Factor
    
End Function

give different results.

 

[Andrew Poulsom]

I wouldn't know without timing it, but worksheet functions are pretty quick.