Using variables to determine a range for an average.

**blastronaut** · 05-08-2011, 12:13 AM

Hello everybody!

On Sheet1, B2 through B15600 will contain a list of numbers. I want to be able to take an average of a given amount of those cells and enter it into a corresponding cell in C. To do this, I would like to be able to enter 2 numbers on Sheet2 in cells B1 and B2. B1 will govern the range of cells and B2 will determine how many cells up from the cell in Column C that the range will end.

For instance, if I entered the numbers 10 and 10, cell C20 would have the following formula "=Average(B2:B11)".

I would also like it to be auto filled all the way to C15600.

I am not very good at VBA yet but here is the code I have so far. It is giving me a "Compile Error: Expected End of Statement"

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Length1 As Integer
Dim Length2 As Integer

    Length1 = Sheets("Sheet2").Range("B1").Value
    Lenght2 = Sheets("Sheet2").Range("B2").Value

Sheets("Sheet1").Range("b2:b15600").Formula = "=Average("Sheets("Sheet1").Range("b2:b15600").Selection.Offset(-(Length1+Length2), -1).Value":"Sheets("Sheet1").Range("b2:b15600").Selection.Offset(-(Length2), -1).Value")"

End Sub

Can you guys offer any help?

**tigertiger** · 05-08-2011, 12:52 AM

I don't understand the example

For instance, if I entered the numbers 10 and 10, cell C20 would have the following formula "=Average(B2:B11)".

if Sheet2.B1 = 10 and Sheet2.B2=5 , C...=? would be ?

**blastronaut** · 05-08-2011, 01:00 AM

Originally Posted by tigertiger

I don't understand the example

if Sheet2.B1 = 10 and Sheet2.B2=5 , C...=? would be ?

In that case it would be "=AVERAGE(B5:B14)" It would be a 10 cell range ending 5 cells above the current cell.

I've also updated my code adding "&" due to examples I've seen. The new code looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Length1 As Integer
Dim Length2 As Integer

    Length1 = Sheets("Sheet2").Range("B1").Value
    Lenght2 = Sheets("Sheet2").Range("B2").Value

Sheets("Sheet1").Range("b2:b15600").Formula = "=Average(" & Sheets("Sheet1").Range("b2:b15600").Selection.Offset(-(Length1 + Length2), -1).Value & ":" & Sheets("Sheet1").Range("b2:b15600").Selection.Offset(-(Length2), -1).Value & ")"

End Sub

Now I am getting "Run-time error '438':

Object doesn't support this property or method"

**tigertiger** · 05-08-2011, 01:09 AM

I think: don't need VBA, using only formula

at cell C2 (sheet1), enter the formula
=IF(ROW()-1<Sheet2!$B$1+Sheet2!$B$2,"",AVERAGE(INDIRECT("B"&(ROW()-Sheet2!$B$1+1)&":B"&ROW())))
and fill-copy for C3:C.... (... end of column C corresponding with column B)

**blastronaut** · 05-08-2011, 01:27 AM

---See next post---

**blastronaut** · 05-08-2011, 01:40 AM

Originally Posted by tigertiger

I think: don't need VBA, using only formula

at cell C2 (sheet1), enter the formula
=IF(ROW()-1<Sheet2!$B$1+Sheet2!$B$2,"",AVERAGE(INDIRECT("B"&(ROW()-Sheet2!$B$1+1)&":B"&ROW())))
and fill-copy for C3:C.... (... end of column C corresponding with column B)

Actually, it seems like it is working backwards. Let's use the values on Sheet2, B1=5 and B2=10.

The formula in Sheet1, Cell C16 should be =Average(B2:B6) but it is actually this =Average(B7:B15)

Can you fix that? I am too much of a newbie to see how to change it.

Thanks!

**tigertiger** · 05-08-2011, 01:54 AM

Originally Posted by blastronaut

Actually, it seems like it is working backwards. Let's use the values on Sheet2, B1=5 and B2=10.

The formula in Sheet1, Cell C16 should be =Average(B2:B6) but it is actually this =Average(B7:B15)

Can you fix that? I am too much of a newbie to see how to change it.

Thanks!

no It ACTUALLY is =Average(B12:B16)

because B1 is range to average, B2 is ... as you said at above post

what is about C17,C18,.....

**tigertiger** · 05-08-2011, 02:00 AM

Originally Posted by blastronaut

Can you fix that? I am too much of a newbie to see how to change it.
Thanks!

If you like that, you can use the following formula for cell C2:
=IF(ROW()-1<Sheet2!$B$1+Sheet2!$B$2,"",AVERAGE(INDIRECT("B"&(ROW()-Sheet2!$B$1+1-Sheet2!$B$2)&":B"&(ROW()-Sheet2!$B$2))))

and then fill-Copy for C3,C4,......

IF YOU want to know the range in D2 you paste the formula, and then file D3,D4,..... =>> it will show the range to caculate AVERAGE
=IF(ROW()-1<Sheet2!$B$1+Sheet2!$B$2,""," Average of B"&(ROW()-Sheet2!$B$1+1-Sheet2!$B$2)&":B"&(ROW()-Sheet2!$B$2) )

is that true: ?

**blastronaut** · 05-08-2011, 05:58 PM

Originally Posted by tigertiger

If you like that, you can use the following formula for cell C2:
=IF(ROW()-1<Sheet2!$B$1+Sheet2!$B$2,"",AVERAGE(INDIRECT("B"&(ROW()-Sheet2!$B$1+1-Sheet2!$B$2)&":B"&(ROW()-Sheet2!$B$2))))

and then fill-Copy for C3,C4,......

IF YOU want to know the range in D2 you paste the formula, and then file D3,D4,..... =>> it will show the range to caculate AVERAGE
=IF(ROW()-1<Sheet2!$B$1+Sheet2!$B$2,""," Average of B"&(ROW()-Sheet2!$B$1+1-Sheet2!$B$2)&":B"&(ROW()-Sheet2!$B$2) )

is that true: ?

Perfect! Thank you so much!

Using variables to determine a range for an average.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Using variables to determine a range for an average.

Re: Using variables to determine a range for an average.

Re: Using variables to determine a range for an average.

Re: Using variables to determine a range for an average.

Re: Using variables to determine a range for an average.

Re: Using variables to determine a range for an average.

Re: Using variables to determine a range for an average.

Re: Using variables to determine a range for an average.

Re: Using variables to determine a range for an average.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions