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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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,

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

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))




On Nov 12, 9:59 pm, tbraddy <TBra...@gmail.com> wrote:
> 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

Thanks,

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

Thanks for the help.

Todd