I have a couple thousand rows of data grouped into eight transaction types.
Column A is the type and Column B is the amount. I would like average the
amounts of each of the groups. Can you give me an idea how best to approach
this task? It is repetitive in that this report is prepared on a weekly basis.

I recommend a Pivot Table

Select your data range

Then....from the Excel main menu:
<Data<Pivot Table
Use: Excel
Select your data
Click the [Layout] button

ROW: Drag the TYPE field here
DATA: Drag the AMOUNT field here
If it doesn't list as Average of AMOUNT...dbl-click it and set it to Average
Click [OK]

Select where you want the Pivot Table...and you're done

That will create a table listing each TYPE and the Average of Amount per TYPE.

To refresh the Pivot Table, just right click it and select Refresh Data

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Mike" wrote:

I have a couple thousand rows of data grouped into eight transaction types.
Column A is the type and Column B is the amount. I would like average the
amounts of each of the groups. Can you give me an idea how best to approach
this task? It is repetitive in that this report is prepared on a weekly basis.

Ron, thanks, that is a straightforward method of dealing with the report; one
that I can certainly use. I was hoping for something that would automatically
recognize the unique elements in the first column and group by those
elements. I might be dreaming, but since I have to repeat it so often, I was
hoping that there would be a macro os something that I could just point to
the worksheet and have it spit out results. :-)

"Ron Coderre" wrote:

I recommend a Pivot Table

Select your data range

Then....from the Excel main menu:
<Data<Pivot Table
Use: Excel
Select your data
Click the [Layout] button

ROW: Drag the TYPE field here
DATA: Drag the AMOUNT field here
If it doesn't list as Average of AMOUNT...dbl-click it and set it to Average
Click [OK]

Select where you want the Pivot Table...and you're done

That will create a table listing each TYPE and the Average of Amount per TYPE.

To refresh the Pivot Table, just right click it and select Refresh Data

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Mike" wrote:

I have a couple thousand rows of data grouped into eight transaction types.
Column A is the type and Column B is the amount. I would like average the
amounts of each of the groups. Can you give me an idea how best to approach
this task? It is repetitive in that this report is prepared on a weekly basis.

It is not difficult to custom make a macro you desired.
Assuming the eight types are always correctly entered each time
we would

sort whole of the two columns 'this gets rid of blank rows also
for each type
find first row of type#
find last row of type#
compute nr of items
compute total amount
average of type# = amount / nr of item
next type

Regards

"Mike" wrote in message
...
Ron, thanks, that is a straightforward method of dealing with the report;
one
that I can certainly use. I was hoping for something that would
automatically
recognize the unique elements in the first column and group by those
elements. I might be dreaming, but since I have to repeat it so often, I
was
hoping that there would be a macro os something that I could just point to
the worksheet and have it spit out results. :-)

"Ron Coderre" wrote:

I recommend a Pivot Table

Select your data range

Then....from the Excel main menu:
<Data<Pivot Table
Use: Excel
Select your data
Click the [Layout] button

ROW: Drag the TYPE field here
DATA: Drag the AMOUNT field here
If it doesn't list as Average of AMOUNT...dbl-click it and set it to
Average
Click [OK]

Select where you want the Pivot Table...and you're done

That will create a table listing each TYPE and the Average of Amount per
TYPE.

To refresh the Pivot Table, just right click it and select Refresh Data

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Mike" wrote:

I have a couple thousand rows of data grouped into eight transaction
types.
Column A is the type and Column B is the amount. I would like average
the
amounts of each of the groups. Can you give me an idea how best to
approach
this task? It is repetitive in that this report is prepared on a
weekly basis.

Thank you very, very much!

"PY & Associates" wrote:

It is not difficult to custom make a macro you desired.
Assuming the eight types are always correctly entered each time
we would

sort whole of the two columns 'this gets rid of blank rows also
for each type
find first row of type#
find last row of type#
compute nr of items
compute total amount
average of type# = amount / nr of item
next type

Regards

"Mike" wrote in message
...
Ron, thanks, that is a straightforward method of dealing with the report;
one
that I can certainly use. I was hoping for something that would
automatically
recognize the unique elements in the first column and group by those
elements. I might be dreaming, but since I have to repeat it so often, I
was
hoping that there would be a macro os something that I could just point to
the worksheet and have it spit out results. :-)

"Ron Coderre" wrote:

I recommend a Pivot Table

Select your data range

Then....from the Excel main menu:
<Data<Pivot Table
Use: Excel
Select your data
Click the [Layout] button

ROW: Drag the TYPE field here
DATA: Drag the AMOUNT field here
If it doesn't list as Average of AMOUNT...dbl-click it and set it to
Average
Click [OK]

Select where you want the Pivot Table...and you're done

That will create a table listing each TYPE and the Average of Amount per
TYPE.

To refresh the Pivot Table, just right click it and select Refresh Data

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Mike" wrote:

I have a couple thousand rows of data grouped into eight transaction
types.
Column A is the type and Column B is the amount. I would like average
the
amounts of each of the groups. Can you give me an idea how best to
approach
this task? It is repetitive in that this report is prepared on a
weekly basis.

In the menu select DataSubtotals


"Mike" wrote:

I have a couple thousand rows of data grouped into eight transaction types.
Column A is the type and Column B is the amount. I would like average the
amounts of each of the groups. Can you give me an idea how best to approach
this task? It is repetitive in that this report is prepared on a weekly basis.