l0gic

03-03-2011, 11:59 AM

Hi all,

I'm at a loss here. I'm sure what I want to do can be done, just not sure how to go about it. I'm not the best in Excel, I'd prefer Perl or C to figure these things out however I need this on in Excel.

I have several columns of numbers that go for atleast 2000 rows.

As an example:

A B

4 6

2 15

7 27

5 6

1 2

13 18

1 2

5 10

5 10

3 6

1 19

2 3

1 14

1 7

6 9

Now what I want to do for each column is for example take what Row() numbers that the number 1 appears, i.e. 5, 7, 11, 13, 14 and then find first what the difference is in row numbers, and then the average difference.

So the difference between..

5 & 7 is 2

7 & 11 is 4

11 & 13 is 2

13 & 14 is 1

And so on..

And then use Average() to do:

Average(2,4,2,1) -- (Equals 2.25 in this case.)

Except on a much larger scale, as I said, about 2000 rows.

Can this be done, anyone know how?

Did I even make sense?

I'm at a loss here. I'm sure what I want to do can be done, just not sure how to go about it. I'm not the best in Excel, I'd prefer Perl or C to figure these things out however I need this on in Excel.

I have several columns of numbers that go for atleast 2000 rows.

As an example:

A B

4 6

2 15

7 27

5 6

1 2

13 18

1 2

5 10

5 10

3 6

1 19

2 3

1 14

1 7

6 9

Now what I want to do for each column is for example take what Row() numbers that the number 1 appears, i.e. 5, 7, 11, 13, 14 and then find first what the difference is in row numbers, and then the average difference.

So the difference between..

5 & 7 is 2

7 & 11 is 4

11 & 13 is 2

13 & 14 is 1

And so on..

And then use Average() to do:

Average(2,4,2,1) -- (Equals 2.25 in this case.)

Except on a much larger scale, as I said, about 2000 rows.

Can this be done, anyone know how?

Did I even make sense?