Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum, Average, and Count from imported .txt file
I must import a large text file with one record for each transaction, then
grab all transactions of the same type within a data range. This I can do. But within this group I also need to then break into sub groups based on value ranges (1-10,11-20, etc) and then to count the number of transactions and average the value of all transactions within each subgroup. I think I need to keep each record in each subgroup somewhere then do the averaging and then record the count and average for each subgroup on a report. I'm new to excel so any help would be appreciated! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum, Average, and Count from imported .txt file
I think you will need some sort of COUNTIF() or SUMPRODUCT() formula. You
don't provide any details about the layout of your sheet, so generically... Column A - values you want to filter by =COUNTIF(A:A,"=1") - COUNTIIF(A:A,"10") =SUMPRODUCT(--(A1:A1000=1),--(A1:A1000<=10)) Something like that. Does that get you started? More reading on Sumproduct(): http://www.xldynamic.com/source/xld....T.html#classic -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "sdhi" wrote: I must import a large text file with one record for each transaction, then grab all transactions of the same type within a data range. This I can do. But within this group I also need to then break into sub groups based on value ranges (1-10,11-20, etc) and then to count the number of transactions and average the value of all transactions within each subgroup. I think I need to keep each record in each subgroup somewhere then do the averaging and then record the count and average for each subgroup on a report. I'm new to excel so any help would be appreciated! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum, Average, and Count from imported .txt file
Hi,
You may create a pivot table. Drag the values column to the row area and transactions to the data area. Now click on any cell in the row area and go to pivot table drop down on the pivot table toolbar. Select Group and Show detail Group. Fill in the required input. Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "sdhi" wrote in message ... I must import a large text file with one record for each transaction, then grab all transactions of the same type within a data range. This I can do. But within this group I also need to then break into sub groups based on value ranges (1-10,11-20, etc) and then to count the number of transactions and average the value of all transactions within each subgroup. I think I need to keep each record in each subgroup somewhere then do the averaging and then record the count and average for each subgroup on a report. I'm new to excel so any help would be appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Zero is missing from imported file. | New Users to Excel | |||
Date from imported file | Excel Discussion (Misc queries) | |||
dealing with csv imported file in excel | Excel Discussion (Misc queries) | |||
CSV file Imported to Excel | Excel Discussion (Misc queries) | |||
can I display the file name of the imported text file in another c | Excel Discussion (Misc queries) |