Transaction type/ Transaction amount in two separate columns

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.
0
Mike1154 (1216)
11/3/2006 7:21:02 PM
excel.newusers 15348 articles. 2 followers. Follow

5 Replies
357 Views

Similar Articles

[PageSpeed] 41

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.
0
11/3/2006 9:29:01 PM
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.
0
Mike1154 (1216)
11/3/2006 10:31:01 PM
In the menu select Data>Subtotals


"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.
0
11/4/2006 4:01:02 PM
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" <Mike@discussions.microsoft.com> wrote in message
news:F492F8AD-DE4F-4458-B016-7FB56BC7AFE7@microsoft.com...
> 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.


0
pynasocas (62)
11/5/2006 12:46:49 AM
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" <Mike@discussions.microsoft.com> wrote in message
> news:F492F8AD-DE4F-4458-B016-7FB56BC7AFE7@microsoft.com...
> > 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.
> 
> 
> 
0
Mike1154 (1216)
11/9/2006 2:38:03 PM
Reply:

Similar Artilces:

Transaction to read when no actual transaction
I have an account called 'Honda accord'. It is an asset account that has a loan associated with it. The loan account is 'Honda loan.' I imported a transaction into Honda accord and says i have 1 transaction to read. I have view all dates/transactions selected and nothing comes up. The box to the left says i still have a transaction to read and I cant find the darn thing. Any Ideas???? -- Stephen and Erica Chenelle In microsoft.public.money, Stephen & Erica Chenelle wrote: >I have an account called 'Honda accord'. It is an asset account that has a >...

Type Mismatch 04-12-10
Hi All, I get the type mismatch error on this line of code TmpCB.Sheets("03-10").Cells(z, 2).Value = OldCB.Sheets("032010").Cells(x, 2).Value + " - " + OldCB.Sheets("032010").Cells(x, 3).Value I know that cells(x,2) at this poin in the code is "check run" and Cells(x,3) is "4277" I'm sure it's because "check run" is text and "4277" is a number, but I can't figure a way around it. I've tried doing .text instead of .value but it still didn't work. Can someone h...

Paid Transaction Removal not removing some transactions
When running Paid Transaction Removal two transactions for an account were not removed to history while seemingly identical transactions in another account were. As I understand the rules of paid transaction removal, a transaction will be removed if it meets the following criteria. 1. If it's an invoice it must be fully paid. 2. If it's a payment, the document amount must be fully applied to one or more invoices. 3. The removal date must be after the document date. The documents are debit notes, are fully paid off (CURTRXAM = .00000) and the document dates precede the cut-off d...

Highlight Duplicate Column
i want a vba code to highlight the duplicate value... the code needs to be dynamic i.e thru userform when i select a column it has look into tat particular column and highlight it ... ...

Excel text to columns
In Excel 2000 I downloaded my annual TD Waterhouse satement. It all downloads to one cel and trying to get just the last column into a susable for and separate it into a single column of its own is difficult. Thewre must be a simple way. Is here someone who might be able to talk back and forth with me to resolve? Thanks, Fil You can use this macro. Sub extractlastcol() For Each c In Range("a2:a161") 'Selection x = Right(c, InStr(3, c, " ")) If IsNumeric(x) Then c.Offset(, 1) = x Next End Sub -- Don Guillett SalesAid Software donaldb@281.com "fil"...

Setting up separate network on same wire with RRAS
I want to set up a phantom network, that is, a copy of our existing network computers, eg print server, file server, exchange. I want to put them all on a couple computers as Virtual machines. This is for testing purposes. I will create the machines and then backup the original servers and restore them to the Virutal Machines. The key issue for me is how can I keep them separate. Eg that the one network does not see the other network. So far I have restored a print server to the phantom network. The host is on our 10.1.0.x network. The VM is has two Virtual nics. One is the outsit...

Money Plus Deluxe 2008 Can't Change Account Type
I set up a 401k account as an asset account, instead of a retirement account in an earlier version of Money. I did it that way because that version made it hard to manage if listed properly. I can't figure out how to change it to a retirement account in Money Plus Deluxe 2008. It isn't an option under Change Account Settings. However, if I look at my checking account under Change Account Settings, there is an option to change it to a retirement account. Why can't I change an asset account to a retirement account? -- Jeannine ...

V10 - Transaction by debtor enquiry report
Hi, Anyone any ideas why when printing the transaction by debtor enquiry report, the last digit of the amount is dropped off? This is when printed as A4 - payables is okay going to same printer. Okay when displayed to screen - only lost when actually printed. Thanks Jean -- JB Is the last digit perhaps trying to print outside the 'printable' area of the printer? If you do not use A4 paper does it work? Do you have the A4 module installed? "JB" wrote: > Hi, > > Anyone any ideas why when printing the transaction by debtor enquiry report, ...

PUB File
My associate created a document (2 column) and sent it to me for final touch ups, along with a PDF representation of the file. When I open the PUB, the single page document now spans two pages - unlike the PDF. We are using a simple font - available on both PCs. When I've made the text fit on a single page, print to PDF, and return PUB and PDF file to him, he sees the PUB with extra white space in the lower left right - which I guess I expected based on the way the file spilled to two pages for me, and not for him. Is there something I can look at to better understand what could be ...

PM Transaction Work vs Open
What is the difference between PM_Transaction_WORK and PM_Transaction_OPEN? Thanks, Steven work = saved open - posted "Steven" wrote: > What is the difference between PM_Transaction_WORK and PM_Transaction_OPEN? > > Thanks, > Steven As an add on, the work also includes recurring transactions. "Doug" wrote: > work = saved > open - posted > > "Steven" wrote: > > > What is the difference between PM_Transaction_WORK and PM_Transaction_OPEN? > > > > Thanks, > > Steven Thanks, Guys! Steven "klewis&quo...

How are transactions automatically assigned to categories?
When I download my credit card statements into Money, I notice that some of them are automatically assigned to categories. How does Money do this? Is the information included in the info I download from my bank, or are there rules within money? More importantly, how can I add my own rules to auto- categorize things? Thanks! -Aaron ...

comapring two worksheets
i would like to compare the contents of two worksheets is this possible thank you depends what your need to do, if looking for "math" changes its' one thing, if your sheet has different number of rows and columns then that's much larger. if you have 2 sheets exactly the same, you can have a 3rd sheet do a comparison of the cells on third sheet a1 of sheet 1 minus a1 of sheet 2 will tell you if the math results in a different value. Before you copy the A1 formula to the rest of teh sheet, use condtional formatting to highlight the background if the value is not ze...

Receivables transactions not aging individual transactions
Looking at smartlist, the Receivables transactions have a search/favourite added, including the aging periods. When you look at the search, it doesn't age the individual transactions, but instead ages the entire customers balance. I would expect it to show the aged balance per transation. Assessment: With how Smartlist is currently designed, the default smartlist object pulls from the Summary table for those aging period amounts. This is the reason why it does not show the aging for individual transactions. I know that the ability to see the aging of the individual accounts would b...

Can you limit the amount of times a calc is performed on certain cells
Is there anyway of telling Excel to perform a calculation on certain cells only once. I have a spreadsheet that performs calculations that include a single value stored in a cell. I now want to change the value of this cell to apply to new data being added to the sheet but I don't want to change the results of past calculations. Any help would be appreciated. -- n_davies n_davies: It appears to me you wish to track scenarios. You can learn more about scenarios using Microsoft's help function. Personally, scenarios are not as friendly when sending to a supervisor. In the ...

Uniform % increase in column.
Wondering if anyone can help. I'm probably missing something silly bu I need to increase all the values in a column by a different % ever month. Is there a way that I can do this in one go rather tha manually having to calculate the new value? Thanks Su -- Message posted from http://www.ExcelForum.com Hi one way: - enter for example the value 1.05 (for a 5% increase) in an empty cell - copy this cell - select your values - goto 'Edit - Paste Special' and choose 'Multiply' Note: The original values are lost after this procedure >-----Original Message----- >Wo...

How to add more Journal type entries
Hello Can any one tell me how to add journal type entries? Thank you. The following article tells you how: http://www.inquiry.com/techtips/exo_pro/10min/10min0999.asp -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:ubFcYGE9EHA.3640@tk2msftngp13.phx.gbl, msw wrote: > Hello > > Can any one tell me how to add journal type entries? > > Thank you. ...

TRANSACTION and SELECT *
Hi all I have a question about SQL Transactions: For example, I have a table with 3 rows (ID 1, 2 and 3). Now I insert a new row within BEGIN TRANSACTION, and I do not commit yet. In another SQL Session, I try to select data. When I do SELECT * FROM Table, it is blocked. When I do SELECT * FROM Table WHERE ID = 2 I get a result. How can I make SELECT * FROM Table possible even when there is a open Transaction ? Of corse I only expect to get the ID's 1, 2 and 3 back and not the new row. I have looked about IsolationLevel, but this does not help. Thanks for any comments ...

Two mailboxes opening up
This is more of an annoyance than a problem but could be the reason I keep getting send/receive errors. In my folder list, I have two versions of my mailbox open, they are the exact same mailbox, and I checked my exchange server to make sure there weren't duplicates in my storage group. Both copies are identicle, and I am unable to right click and close the second mailbox. Anyone have any suggestions? Thanks. Nate Weldon <NateWeldon@discussions.microsoft.com> wrote: > This is more of an annoyance than a problem but could be the reason I > keep getting send/receive err...

Debugging SQL Server 2005 Transact SQL
I have an urgent requirement to debug Transact-SQL in SQL Server 2005 and I have a couple of questions. 1. Are there any *free* programs that will allow me to debug (step through line-by-line, look at cursor results, variables, etc). 2. If the answer is 'no' and Visual Studio is the least expensive alternative, which versions are compatible and have debugging capability? Thank you very much in advance. allanc (allan.for.g.groups@gmail.com) writes: > I have an urgent requirement to debug Transact-SQL in SQL Server 2005 > and I have a couple of questions. > 1. Are th...

Receivables Transaction Entry #2
Anyone have any idea why the Address ID does not get saved on the Receivables Transaction Entry when posted? -- Japheth Nolt Microsoft SBF Specialist Landis Computer www.landiscomputer.com 5/4/2007 3:27:40 PM There is no field available in the Receivables Open table to store it! I have put in this suggestion since version 5.5, and it still hasnt happened. You will have to develop a customisation to track it in a parrellel table. (Or use VBA & DUOS table) ------ Robert "Japheth Nolt" <japheth.remove@landiscomputer.com> wrote in message news:xn0f5t47t3uqcr000@msnews....

Replace the column letters with my own heading
I want to know how i can i replace the column letters so i can put in my heading so that i always know which coumn i'm in and which question i'm answering when i'm entering data further down the page. Or any way that i can get my headings to follow down the page as i'm entering data so i can always see it no matter how many rows down i am. If your headings are in row 1 Click in cell A2 and Goto Window>Freeze Panes. Freeze Panes freezes anything above and to the left of the active cell. HTH Martin "Catter77" <Catter77@discussions.microsoft.com> wr...

how to type multiple adresses on an avery 5160
I have avery 5160 sheet and am using publisher 2002 and cannot figure how to put multiple adresses on one sheet This is a 2003 Publisher help file, but it works for 2002 too. Mail and catalog merge http://office.microsoft.com/en-us/publisher/CH062524751033.aspx If you have already done the merge and looked at print preview, all the labels will look the same. This is a Publisher bug. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "ddent" <ddent@discussions.microsoft.com> wrote in message news:BB065B39-02F3-42DA-9C...

Amount in words #2
Hi, I need to convert Purchase Order Document total amount to be converted in Words. Document total amount is already a calculated field, so what function need to be adopt??? Please explain step by step. Regards, Sheikh Asif Try using the Report Writer User Defined function below. It can be accessed from a Calculated field by adding a User Defined function from the System series. RW_ConvertToWordsAndNumbers() The parameters are as follows: in currency IN_Value; { Value of Currency } in string IN_Currency; { Currency ID of Currency - Leave Blank for Functional } in integer IN_Mode;...

Rounding on Investment transactions
I've seen this problem for quite a while in Monday (at least 01, 02, 03, and 04). But when I'm entering my transactions, often times the final amount (shares*cost+comm) don't equal out, it's always the matter of 1 penny, but I'm a bit anal that way. For instance. 0.78800 shares at 27.8900 per share. Fully worked out it comes to 21.97732. So when Money does the transaction it rounds it to 21.98 (makes perfect sense). My brokerage relays the cost as 21.97. What options do I have to make these transactions actually work out? Since I can't do a negative commis...

Expand and Shrink Rows and columns to hide information.
I have a spreadsheet with rows 1 & 2 and columns numbered 1 & 2. In column 1 I have the ability to click a plus sign (+) to expand information in hidden rows. Along the top I also have the ability to click a plus (+) sign in column 1 to expand hidden columns. It appears that column 2 denotes those rows to be hidden by a "." Ok how do I set this up? Data>Group and Outline Gord Dibben MS Excel MVP On Tue, 5 Jan 2010 07:29:02 -0800, jf1847 <jf1847@discussions.microsoft.com> wrote: >I have a spreadsheet with rows 1 & 2 and columns number...