Data Selection and Averaging

[tbraddy]

I have some data that is listed in the lower portion of a worksheet
(rows 201+) and want to grab it based on user input into a cell. I
also want to calculate an average of that data.

ie
Value
Start 30 lookup value for 30
End 60 lookup value for 60
Average calculate average for 30-60

I could just do it =B230 =B260 =average(B230:B260), but having it
update would be much easier
I can do a lookup on the Start and End data, but the average is not
as
easy.
Is there a way to say =B(200 + value of Start), =B(200+End),
=average(B(200+start):B200+end))?

Thanks Todd

 

[Mike Fogleman]

With start# in B1, end# in B2, and AVG in B3:
Adjust the ranges to suit your placement

Sub MyAvg()
Dim StartRng As Range, EndRng As Range
Dim MyStr As String
MyStr = "B" & Range("B1").Value + 200
Set StartRng = Range(MyStr)
MyStr = "B" & Range("B2").Value + 200
Set EndRng = Range(MyStr)
Range("B3").Value = WorksheetFunction.Average(StartRng, EndRng)
End Sub


Mike F

 

[tbraddy]

Mike,

I don't know if that will work with my data and what I'm trying to
accomplish.
Here is what the data looks like:

Col 1 Col 2 .... Col
N
Start1 =Col1(start1.value+200) =Col2(start1.value+200)
End 1 =Col1(end1.value+200) =Col2(end1.value+200)
Average 1 =average(start1:end1) =average(start2:end2)

Start M
End M
Average M

It would be really helpful in this if I could define a function and
not have to run a macro with a button or anything.

Todd

 

[chip.gorman]

Well, you did post in the VBA newsgroup...

I think what you want is an OFFSET something like:

=AVERAGE(OFFSET($B$1,Start1+200,0,End1-Start1+1,1))

 

[tbraddy]

Thanks,

That seems to work except that using $B$1 and using the +200 finds
201+Start1.

Thanks for the help.

Todd