Forum Home
Press F1
Thread ID: 116409	2011-03-02 22:59:00	Looking for an Excel expert! (Question to do with averages)	l0gic (6781)	Press F1

Post ID	Timestamp	Content	User
1183083	2011-03-02 22:59:00	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?	l0gic (6781)
1183084	2011-03-02 23:19:00	=AVERAGE(A1:A15) =AVERAGE(A1:B1)	pctek (84)
1183085	2011-03-03 00:26:00	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.	inphinity (7274)
1183086	2011-03-03 00:52:00	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.	nofam (9009)
1183087	2011-03-03 03:08:00	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	MushHead (10626)
1183088	2011-03-03 08:51:00	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.	l0gic (6781)
1