time-rounding,and if then statements

[amcmillio]

I have a spreadsheet that has 2000 points of time listed. The time is in 22 minute incremants. I only want the cells with the time that are closest to the hour. It is never the same. How can I delete the cells that are not closest to the hour without doing it manually? I have 40 other spreadsheets to do the same with. Please help me to find a way to stop doing it manually and get rid of my headache. Is there a formula I can use? I am not use to using time in formulas.

Example:
11:06 (I would keep)
11:28 (I would delete)
11:50 (I would keep)
12:12 (I would delete)
12:34 (I would delete)
12:56 (I would keep)
1:18 (I would delete)
Etc...
This message was edited by amcmillio on 2002-10-09 13:25

 

[Steve Hartman]

On 2002-10-09 14:41, amcmillio wrote:
I am for the most part using the time in between 15 minutes before the hour and 15 minutes after the hour but not always. If you notice I would not keep rows 4, 15 and 22 which are in between 15 minutes before amd 15 minutes after the hour.

Ok, Why would you not keep rows 4,15,and 22?
Not trying to be difficult; just trying to understand

 

[Steve Hartman]

The light may be dawning in my brain! Are you saying that for every hh:00 covered by your data, you want to look at the reading before and after hh:00 and just keep one reading for that hour, deleting all other readings?

 

[amcmillio]

You are not being difficult- this is a hard one to understand...
Here you go...

My goal is to keep only the rows that round up or round down to the nearest hour.
I would keep 1 because it would represent 11:00. I would not keep 2. I would keep 3 because it is closer to 12:00 than 4. I would not keep 5. I would keep 6 because it would represent 1:00. I would not keep 7 or 8. I would keep 9 because it would represent 2:00. Etc... Does it make sense now?

 

[amcmillio]

YES!!!!

 

[Steve Hartman]

Guess what...I have another question.

What if your example data had started with row 3 where the time was 11:50. Would you want to count that row for 11:00 as well as 12:00 or just for 12:00?

 

[amcmillio]

11:50 is too far away from 11:00. I would then have NO data for 11:00. I would count it for 12:00.

 

[Dave Patton]

The following is an Array Formula; enter it with Ctrl-Shift-Enter

1. I named the range of times "rT"

EDIT I also put 1/24 into a cell named "Hr"

2. B1 has 11:00 and B2 has 12:

=OR(MAX(IF(((rT-($B$1+(INT(ROW()/3))*Hr))^2=MIN((rT-($B$1+(INT(ROW()/3))*Hr))^2))*rT,rT))=A1,MAX(IF(((rT-($B$2+(INT(ROW()/3))*Hr))^2=MIN((rT-($B$2+(INT(ROW()/3))*Hr))^2))*rT,rT))=A1 )

Please advise if this formula works for you.

Can the time be in 24 hour format then test
for 1:00 PM is really a test for 13:00?

The formula yields True or False; Filter and then Delete.

Dave
This message was edited by Dave Patton on 2002-10-09 16:19
This message was edited by Dave Patton on 2002-10-09 16:37

 

[amcmillio]

I will check it out and get back to you. Thank you.

 

[amcmillio]

yes it can be in military time "24:00"

 

[eliW]

Hi amcmillio,
Simple!
Steve was on the right way. As your intervals are of 22 minutes - the times close to any full hour MUST fall between xx:49 and xx:11.
Therefore we can check with MINUTE function if the minutes are less/equal 11 OR greater/equal 49.
If yes KEEP, if no DELETE.
Look at my example below.

Now SORT your data on column E to have all the rows which are assignd "keep" in one group and drop the rest.
Good luck,
Eli

Book1
	A	B	C	D	E
1	Point	Date	Time	Sensor1	Keep/Delete
2	1	8/23/02	11:06:13AM	75.8	Keep
3	2	8/23/02	11:28:13AM	75.6	Delete
4	3	8/23/02	11:50:13AM	75.3	Keep
5	4	8/23/02	12:12:13PM	87.8	Delete
6	5	8/23/02	12:34:13PM	92.4	Delete
7	6	8/23/02	12:56:13PM	97.8	Keep
8	7	8/23/02	1:18:13PM	101	Delete
9	8	8/23/02	1:40:13PM	102.9	Delete
10	9	8/23/02	2:02:13PM	104.8	Keep
11	10	8/23/02	2:24:13PM	105.6	Delete
Sheet1

This message was edited by eliW on 2002-10-10 02:22