need formula to pick a value every certain set of minutes from large database
 

Hi

We need formula to pick a value every 2,3,5,10,15 minutes from a large
database with records (rows) every minute. The difficulty is that the
records are not continuous for example:

Time Temp (°C)
12:52:47 15.4
12:53:12 15.5
12:56:43 15.5
12:57:03 15.5
12:58:04 14.3
12:59:25 15.9
13:01:42 19.7
13:02:02 20
13:03:08 20.1
13:04:00 17.6

Thanks,




--
Sharkies

need formula to pick a value every certain set of minutes from large database
 

What criteria would you use to pick them out manually?

If you pick out a number every 5 minutes then 10 and 15 are redundant.
I used 2,3,5 as the criteria to determine if the total minutes (since midnight)
for each reading were a multiple of any of those three values.
The formula assumes the time starts in B6 and the Temp in C6...
=IF(MATCH(0,MOD(ROUND(B6*1440,0),{"2","3","5"}),1) ,C6,"")
It extracted seven of the ten temperatures.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"Sharkies"
wrote in message
Hi
We need formula to pick a value every 2,3,5,10,15 minutes from a large
database with records (rows) every minute. The difficulty is that the
records are not continuous for example:

Time Temp (°C)
12:52:47 15.4
12:53:12 15.5
12:56:43 15.5
12:57:03 15.5
12:58:04 14.3
12:59:25 15.9
13:01:42 19.7
13:02:02 20
13:03:08 20.1
13:04:00 17.6

Thanks,
--
Sharkies

need formula to pick a value every certain set of minutes from large database
 

Correction: the Match type should be zero not one...
=IF(MATCH(0,MOD(ROUND(B6*1440,0),{"2","3","5"}),0) ,C6,"")
'--
Jim Cone