Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
averages
I am trying to find the averages of a certain range. I
have a spreadsheet that takes information from other sheets and copy and pastes a column to my summary page. I need to find the averages of the information on the summary page. The only problem is that the range of information changes. It always has the same number of rows, but the number of columns change. Each column is for a different company, and there are not always the same number of companies. So, how do I write a macro that will find out how many columns it needs to find the average for. Thanks, David |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
averages
David,
Try this for size: Lets say that we can expect up to 26 fields to be populated with data and up to 100 records. Say we paste the data into range A1:Z100. We can read the average of all of the data in cell A101 simply with =average (A1:Z100). This will give the correct average for the entire data set even if there is only 1 field populated. ...If I have misunderstood the problem, please clarify David -----Original Message----- I am trying to find the averages of a certain range. I have a spreadsheet that takes information from other sheets and copy and pastes a column to my summary page. I need to find the averages of the information on the summary page. The only problem is that the range of information changes. It always has the same number of rows, but the number of columns change. Each column is for a different company, and there are not always the same number of companies. So, how do I write a macro that will find out how many columns it needs to find the average for. Thanks, David . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
averages
I am sorry I didn't explain very well. I need to find the
average of the rows. There is always the same number of rows, but the number of columns changes. So, I need the macro to be able to find out how many columns and then put the averages at the end of each row. If you can help that would be great. Thanks, David -----Original Message----- David, Try this for size: Lets say that we can expect up to 26 fields to be populated with data and up to 100 records. Say we paste the data into range A1:Z100. We can read the average of all of the data in cell A101 simply with =average (A1:Z100). This will give the correct average for the entire data set even if there is only 1 field populated. ...If I have misunderstood the problem, please clarify David -----Original Message----- I am trying to find the averages of a certain range. I have a spreadsheet that takes information from other sheets and copy and pastes a column to my summary page. I need to find the averages of the information on the summary page. The only problem is that the range of information changes. It always has the same number of rows, but the number of columns change. Each column is for a different company, and there are not always the same number of companies. So, how do I write a macro that will find out how many columns it needs to find the average for. Thanks, David . . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
averages
David,
Try this, changing the fixed rows and column to suit. I trust the variable names used are reasonably self-explanatory. Sub RowAverage() Dim HdrRow, DataRow1, DataRowLast, FirstDataCol Dim EndCol, TotRows, Fma, FmaRow 'Change these to suit HdrRow = 1: FirstDataCol = 3' (C) DataRow1 = 2: DataRowLast = 10 EndCol = Cells(HdrRow, FirstDataCol).End(xlToRight).Column TotRows = DataRowLast - DataRow1 + 1 Fma = "=Average(r[-" & TotRows & "]C:R[-1]C)" FmaRow = DataRowLast + 1 Range(Cells(FmaRow, FirstDataCol), Cells(FmaRow, EndCol)) = Fma End Sub regards, Don Lloyd -- "david" wrote in message ... I am trying to find the averages of a certain range. I have a spreadsheet that takes information from other sheets and copy and pastes a column to my summary page. I need to find the averages of the information on the summary page. The only problem is that the range of information changes. It always has the same number of rows, but the number of columns change. Each column is for a different company, and there are not always the same number of companies. So, how do I write a macro that will find out how many columns it needs to find the average for. Thanks, David |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
averages | Excel Discussion (Misc queries) | |||
Averages | Excel Discussion (Misc queries) | |||
30, 60 or 90 day averages | Excel Worksheet Functions | |||
averages | Excel Discussion (Misc queries) | |||
Averages | Excel Programming |