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

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.

--
sb
"Fidelis" wrote in message
...
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

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


-----Original Message-----
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.

--
sb
"Fidelis" wrote in message
...
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


.

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.

--
sb
"Fidelis" wrote in message
...
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


-----Original Message-----
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.

--
sb
"Fidelis" wrote in message
...
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


.

Fidelis,

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

--
sb
"Fidelis" wrote in message
...
This worked wonderfully,

thanks a whole bunch steve

Fidelis
-----Original Message-----
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.

--
sb
"Fidelis" wrote in message
...
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


-----Original Message-----
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.

--
sb
"Fidelis" wrote in message
...
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


.



.