assigning values by intervals
 

I have data that I would like to assign a number to based on the interval in
which the data's time stamp falls. I am using five minute intervals so I
want to assign the data a number from 1-288 (representing all the 5 min
intervals in a 24 hour day). This is a little to complex for nesting IF
statements. Can I set up a table with a column showing the begining of the
interval and another showing the end with a third containing the number/code
and use some sort of LOOKUP function? Here is an example:

If the data in one row is time stamped 0:06:33 (six minutes and 33 seconds
after midnight) it would fall in the range between 0:05:00 and 0:09:59 which
is the second interval in the table and would therefore return a value of "2".

I am not quite sure how to get the 'between' logic to work since I am not
looking up a set value, like what is normally used for VLOOKUP etc...

Thank you, much appreciation.

Pete

Pete,

How about just using math? For example, if a time is entered in cell A1, use the formula

=INT(A1*24*60/5)+1

The A1*24*60 converts the time to minutes the /5 gets it to the five minute interval, and the +1
accounts for the times below 5 minutes INT'ing to 0.

Nothe that this formula will give 2 for the exact value of 0:05:00, which is what you desired.
Other solutions, such as using ROUNDUP, may behave differently at the break points.

HTH,
Bernie
MS Excel MVP


"Pete at FMR" <Pete at wrote in message
...
I have data that I would like to assign a number to based on the interval in
which the data's time stamp falls. I am using five minute intervals so I
want to assign the data a number from 1-288 (representing all the 5 min
intervals in a 24 hour day). This is a little to complex for nesting IF
statements. Can I set up a table with a column showing the begining of the
interval and another showing the end with a third containing the number/code
and use some sort of LOOKUP function? Here is an example:

If the data in one row is time stamped 0:06:33 (six minutes and 33 seconds
after midnight) it would fall in the range between 0:05:00 and 0:09:59 which
is the second interval in the table and would therefore return a value of "2".

I am not quite sure how to get the 'between' logic to work since I am not
looking up a set value, like what is normally used for VLOOKUP etc...

Thank you, much appreciation.

Pete

I think that will work.
Who would have thought math could be useful!
Thanks

Pete

"Bernie Deitrick" wrote:

Pete,

How about just using math? For example, if a time is entered in cell A1, use the formula

=INT(A1*24*60/5)+1

The A1*24*60 converts the time to minutes the /5 gets it to the five minute interval, and the +1
accounts for the times below 5 minutes INT'ing to 0.

Nothe that this formula will give 2 for the exact value of 0:05:00, which is what you desired.
Other solutions, such as using ROUNDUP, may behave differently at the break points.

HTH,
Bernie
MS Excel MVP


"Pete at FMR" <Pete at wrote in message
...
I have data that I would like to assign a number to based on the interval in
which the data's time stamp falls. I am using five minute intervals so I
want to assign the data a number from 1-288 (representing all the 5 min
intervals in a 24 hour day). This is a little to complex for nesting IF
statements. Can I set up a table with a column showing the begining of the
interval and another showing the end with a third containing the number/code
and use some sort of LOOKUP function? Here is an example:

If the data in one row is time stamped 0:06:33 (six minutes and 33 seconds
after midnight) it would fall in the range between 0:05:00 and 0:09:59 which
is the second interval in the table and would therefore return a value of "2".

I am not quite sure how to get the 'between' logic to work since I am not
looking up a set value, like what is normally used for VLOOKUP etc...

Thank you, much appreciation.

Pete