PDA

View Full Version : Looking for an Excel expert! (Question to do with averages)



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?

pctek
03-03-2011, 12:19 PM
=AVERAGE(A1:A15)
=AVERAGE(A1:B1)

inphinity
03-03-2011, 01:26 PM
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.

nofam
03-03-2011, 01:52 PM
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.

Yup - VBA will do this quite easily, but it will need to incrementally loop through your data, so if it's not written well it'll take donkey's years to run.

I'd suggest you post it over on the Mr Excel forum - you're more likely to get a quick response there.

MushHead
03-03-2011, 04:08 PM
something like this?

(excuse no comments - in a rush!)



Public Function RowAverage(TestValue As Variant, MyData As Range) As Variant
Dim offset As Integer, last_ofs As Integer
Dim count As Integer
Dim Sum As Variant

offset = 0
last_ofs = -1
count = 0
Sum = 0
For Each Point In MyData
offset = offset + 1
If Point = TestValue Then
If last_ofs = -1 Then
last_ofs = offset
Else
Sum = Sum + (offset - last_ofs)
count = count + 1
last_ofs = offset
End If
End If
Next
If count > 0 Then
RowAverage = Sum / count
Else
RowAverage = CVErr(xIErrNA)
End If
End Function

l0gic
03-03-2011, 09:51 PM
Thanks all for having a look.

Inphinity, that's exactly what I'm trying to do. Couldn't think of an easy way to describe it though!

Nofam, I might go post it over there. Have to think of a better way to explain it though I guess.

MushHead, while that works. I don't think it does as intended. It comes up with very high numbers like 30+ for figures I can see on on about every third to sixth row.

It's tricky. I never took-on VB when I was learning. I might see if I can get away with perling it.