Transaction type/ Transaction amount in two separate columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
weekly basis.
 
Thank you very, very much!

PY & Associates said:
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 said:
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 said:
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


:

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
Click to expand...
Click to expand...
weekly basis.
Click to expand...
 
Back
Top