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

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
.

averages
 

David,

The following is based on:-

All data columns have headers, starting from column 2 (B)
The headers are on row 1
All data rows have descriptions in column 1 (A)
The fist data row is immediately below the header row
the Rw and Col variables reflect this

Sub AveColumn()
Dim Rw, Col, EndCol, BtmRw, Fma, FmaCol
Rw = 1: Col = 2
EndCol = Cells(Rw, Col).End(xlToRight).Column - 1
BtmRw = Cells(Rw + 1, Col).End(xlDown).Row
Fma = "=Average(RC[-" & EndCol & "]:RC[-1])"
FmaCol = EndCol + 2
Range(Cells(Rw + 1, FmaCol), Cells(BtmRw, FmaCol)) = Fma
End Sub

The above places the average formulae in the column immediately to the right
of the last data column.

regards,
Don
--

"david" wrote in message
...
William

I'm sorry I didn't say that I needed it to average each
row with columns B-whatever. I need to figure out how I
can do this with a macro.
Thanks for you help.

David
-----Original Message-----
David

Assuming there number of rows does not exceed 100 and the
last possible
column is DZ then this array formula may help. Enter it
using
Ctrl|Shift|Enter. Note the formula will include zeros
within the average but
not blanks.
{=AVERAGE(IF(B8:DZ100<"",B8:DZ30))}

--
XL2002
Regards

William


"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



.

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
.

.

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