Question to do with averages

[l0gic]

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 posted this on another forum, if I was hard to understand. Try this, it's what I mean.

VBScript? ><

I think I see what you're doing - do you want to work out the average row spacing between "1" appearing in column A, after it's first occurence? If so, there's probably a simpler way to do it.

 

[Ruddles]

Assuming your data starts in row 2, create these formulae:-

In C2: =IF(A2=1,ROW(),"")
In D2: =IF(C2="",D1,C2)
In E2: =IF(D2<>D1,D2-D1,"")

Leave C1:E1 empty. Copy C2:E2 down to the end of your data.

Is that it?

 

[l0gic]

Is that it?

You sir are amazing.. It appears to be working great!

 

[Haseeb Avarakkan]

Try,

=AVERAGE(SMALL(IF(A1:A15=1,ROW(A1:A15)),ROW(INDIRECT("2:"&COUNTIF(A1:A15,1))))-SMALL(IF(A1:A15=1,ROW(A1:A15)),ROW(INDIRECT("1:"&COUNTIF(A1:A15,1)-1))))

Confirmed with CTRL+SHIFT+ENTER