selecting certain cells from large data file

[falcon4]

We have large data dump files created from data aquisition equipment. The data is captured at about 30 times a second.
I'd like to pull from the data about 1 data point for every 1 - 10 seconds.

The data imported into excel OK but the times are carry out 9 places when converted to text.

I need help with writing a formula that will look at a reference value then compare it to a row in a range and look for the closest match, then report the offset value in another column.

0.248167675

0.248166053 52.91
0.248166458 52.91
0.248166863 72.01
0.248167269 66.75
0.248167674 17.74
0.248168079 52.63
0.248168484 72.01
0.248168889 61.49
0.248169294 64.81
0.248169699 35.46
0.248170104 30.48
0.248170509 66.47
0.248170914 60.66
0.248171319 63.98
0.248171725 85.3

here is a sample set, of probably 30000 rows
there are 30 rows per second of data. So if i get 1 value from the second column every 300 rows, the data is easier to manipulate.
I could add another column that creates a pattern say 1-300, and then pull every row that starts with 1. Just a thought

 

[GlennUK]

I could add another column that creates a pattern say 1-300, and then pull every row that starts with 1. Just a thought

Yes, try that. If you need any help with a formula for that, just ask.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[falcon4]

i may be thinking too hard but is there an easy way to take a column of sequential values 10-500 incremented by 10's and have it copy down. I can create the pattern, and drag it down, but thought there must be an "excel" way to define it. I get bored dragging down the pattern for 38000 rows

Gary

 

[SkipVought]

Why not start with a reference point and AVERAGE the values every 10 seconds?

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[xlbo]

In row 2

=if(C1<>300,C1+1,1)

you can then just autofill down....

your formula to extract values is

=INDEX(INDIRECT("B" & ((ROW()-1)*300)+2 & ":B" & (ROW()*300)+1),MATCH(1,INDIRECT("C" & ((ROW()-1)*300)+2 & ":C" & (ROW()*300)+1),0))

However, that seems a little pointless as you can get incrementing 300 row gaps by using:

=INDIRECT("B"&((ROW()-1)*300)+2)

The only caveat to this is that your formula must be entered in row 1 and incremented down as it uses the row number as a multiplier to increment the rows in the formula

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

For instance...
[tt]
5:57:10 =SUMPRODUCT((TIM>=$A2)*(TIM<$A3)*(VAL))/SUMPRODUCT((TIM>=$A2)*(TIM<$A3))
5:57:20
[/tt]
where the TIME range is named TIM and the values range is named VAL and the above summary table is columns A & B.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[falcon4]

i used the method of creating a column with a pattern, and then filtering based on one of the values. That worked OK.
I'm going to pursue the concept of averaging every so many values, meaning take every 50 values, average them, put that average in a new cell
take the next 50, and so on down the value column

 

[xlbo]

Have you tried the formula I supplied - tried and tested

If having the formulae starting in row 1 isn't good for you, simply create a list 1 to x and put the following next to it in col B (start in row 2) - assumes list of numbers in column A on a seperate sheet

=INDIRECT("Sheet1!B"&((A2-1)*300)+2)


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[falcon4]

Geoff,
I tried the formula for formula for creating the 1-300 pattern. that works well. I then used the second formula for extracting values. it created 112 new values. The range of values in that column look correct. But i am unsure of how that formula works. How do i explain to the next person where each new value comes from. Is it extracted from a position in the original list of values?

 

[xlbo]

falcon4 - you actually only need the last formula I provided - you don;t need the formula to create the 1-300

the last one I provided:

=INDIRECT("Sheet1!B"&((A2-1)*300)+2)
--requires a helper column of incrementing numbers 1 to x

or

=INDIRECT("Sheet1!B"&((ROW()-1)*300)+2)

both do the same thing

try entering:
="Sheet1!B"&((ROW()-1)*300)+2

and incrementing it to see what it does but basically, it increments rows in jumps of 300 so you get 1 value for every 300 in your dataset

the INDIRECT part converts text to a range address and therefore allows the actual value in the cell to be returned rather than just the string address


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[falcon4]

perfect..
so the first formula is creating cell adress' in intervals of 300.
the second does the same except it reports back the value in the cell at that adress. vuuurrrry nice..

changing 300 to another variable will allow adjustment of the resolution (number of samples per minute used)of the data set.

thanks a boat load...

Gary

 

[xlbo]

no probs

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question