calculating averages

[Fidelis]

I have columns of data that I want to compute averages for.
The columns start on row 13 col 18.
Neither the number of rows nor the number of columns is
static, but I can pull the number of records and number of
fields from the record set at the time of data retrieval.

Is there a way to dynamically create ranges for these
columns that I can use for the average calculation?

Example avg avg avg avg avg
la di da m1 m2 m3 m4 m5
a a a 1 2 2 2 3
b b b 3 5 4 1 5
c c c 5 8 2 2 3
d d d 7 11 6 1 7
e e e 9 13 1 2 3

I need averages for m1 m2 m3 m4 m5 columns to be stored in
the average. I am trying to dynamically create ranges m1
to m5 or mN.. depending on how many columns are there..
and each mI.. has X rows.. (which is unknown).
Its a bit much, I know, and not very clear, but any help
would be appreciated.. tia

 

[steve]

Fidelis,

use this to find the last row number and last column number
Dim lrow As Long, ccol as Long

lrow = Cells(Rows.COUNT, "R").End(xlUp).Offset(1, 0).Row
ccol = ActiveSheet.Range("IV13").End(xlToLeft).Select
"R" is column 18

than

Dim x as Double
x = Worksheetfunction.Average(Cells(13,18), Cells(lrow,ccol))

you can modify the formulas for each individual column, if needed.

 

[Fidelis]

Thanks a whole bunch Steve. The cells function seems to do
the trick when I am doing straight averages.
Unfortunately, I need to do conditional averages and want
to calculate a dividend and divisor.
avg = dividend/divisor ' provided divisor is not zero
dividend = worksheetfunction.sum(cells(x1, x2), cells
(r1, r2))
divisor = worksheetfunction.countif(for cells in same
range that are non zero)

I tried using the Range(cells(x1, x2), cells(r1,r2))

but that returns application or obj definition error.

thanks again for your assistance

Fidelis

 

[steve]

Fidelis,

If x1, x2, r1, r2 refer to cells in columns x & r than you have confused
Excel. When using Cells notation you need numbers (or defined variables).

If they are cells than you need to use Range notation
That is Sum(Range("X1"),Range("X2"))

let me know if this is the problem.

 

[steve]

Fidelis,

You're very welcome! Glad to be of help!