Floor Function for Time Interval

bruce21

New Member
Joined
Oct 25, 2018
Messages
13
Thanks to all who respond.

I am using the FLOOR function with a 2:30 time interval to group my time cells but because of the rounding it is not 100% accurate for grouping.

See the bold items below. I want to group the records that are within 2:30 from the previous record.

6:12:31 0.2587
6:12:35 0.2587
6:12:40 0.2587
6:16:12 0.2604
6:18:08 0.2622
6:24:50 0.2656
6:24:52 0.2656
6:26:46 0.2674
6:30:10 0.2708
6:30:19 0.2708
6:30:24 0.2708

Is there another more accurate way?

Thanks, BD
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,797
Office Version
  1. 365
Platform
  1. Windows
The numbers posted look fine to me, based on what you've described:

I am using the FLOOR function with a 2:30 time interval ....

But what is it you're actually trying to do?

I want to group the records that are within 2:30 from the previous record.

It sounds like your formula should be calculating time differences between cells?

For the data you've posted, what should the results be?
 

bruce21

New Member
Joined
Oct 25, 2018
Messages
13
The floor function should convert the numeric to within 2:30 minutes of each other. The example above shows it does not convert 100% of the time. This is what I was expecting to see.

6:12:31 0.2587
6:12:35 0.2587
6:12:40 0.2587

6:16:12 0.2604
6:18:08 0.2604

6:24:50 0.2656
6:24:52 0.2656
6:26:46 0.2656

6:30:10 0.2708
6:30:19 0.2708
6:30:24 0.2708
 

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,797
Office Version
  1. 365
Platform
  1. Windows
Assuming your times are in A1:A11, I can replicate your results using:

B1: =FLOOR(A1,2.5/1440)
B2: =IF(A2-INDEX(A$1:A1,MATCH(B1,B$1:B1,))>=2.499/1440,FLOOR(A2,2.5/1440),B1) copy down

Is this what you're looking for? It means that any particular time may be "rounded" differently depending on the distribution of other times in its "group"?

I have assumed all your times will have integral numbers of seconds (?) and used 2.499 to avoid binary floating-point rounding discrepancies.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,768
Messages
5,524,787
Members
409,600
Latest member
Dunnowhatfor

This Week's Hot Topics

Top