Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculating averages
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculating averages
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculating averages
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculating moving averages.. | Excel Worksheet Functions | |||
Calculating Block Averages | Excel Discussion (Misc queries) | |||
Calculating weighted averages | Excel Discussion (Misc queries) | |||
calculating averages | Excel Discussion (Misc queries) | |||
calculating averages | Excel Worksheet Functions |