calculating averages
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
.
|