Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot table based on amount ranges.
Range 2005 2006 2007
0-$99 $500.00 $200.00 $2,500.00 $100-249 $2,500.00 $500.00 $5,000.00 $250-499 $6,000.00 $900.00 $2,500.00 $500-999 $1,000.00 $2,500.00 $5,000.00 $1,000-2,499 $25,000.00 $16,000.00 $2,500.00 $2,500-4,900 $60,000.00 $50,000.00 $30,000.00 $5,000-4,999 $90,000.00 $70,000.00 $50,000.00 $10,000 + $80,000.00 $70,000.00 $100,500.00 I have a spreadsheet full of individual financial transactions from 2005 to 2007, Amount and Date. I need the columns to be the sum of the values per year and the rows to be the various financial levels. I'm not sure how to group the transactions within the monetary ranges. Any help would be appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot table based on amount ranges.
You could create a lookup table to group the amounts in the source data
table. For example with a named range GroupLU: Amt Group 0 $0-$99 100 $100-249 250 $250-499 500 $500-999 1000 $1,000-2,499 2500 $2,500-4,900 5000 $5,000-4,999 10000 $10,000+ Use a VLookup formula in the source data: =VLOOKUP(B2,GroupLU,2) Then, add that field to the pivot table's row area. To show the years, add the Date field to the Row area, and group by Year: http://www.contextures.com/xlPivot07.html Then, move the grouped date field to the column area. Captain Snuggles wrote: Range 2005 2006 2007 0-$99 $500.00 $200.00 $2,500.00 $100-249 $2,500.00 $500.00 $5,000.00 $250-499 $6,000.00 $900.00 $2,500.00 $500-999 $1,000.00 $2,500.00 $5,000.00 $1,000-2,499 $25,000.00 $16,000.00 $2,500.00 $2,500-4,900 $60,000.00 $50,000.00 $30,000.00 $5,000-4,999 $90,000.00 $70,000.00 $50,000.00 $10,000 + $80,000.00 $70,000.00 $100,500.00 I have a spreadsheet full of individual financial transactions from 2005 to 2007, Amount and Date. I need the columns to be the sum of the values per year and the rows to be the various financial levels. I'm not sure how to group the transactions within the monetary ranges. Any help would be appreciated. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot table based on amount ranges.
On Oct 19, 2:16 pm, Debra Dalgleish wrote:
You could create a lookup table to group the amounts in the source data table. For example with a named range GroupLU: Amt Group 0 $0-$99 100 $100-249 250 $250-499 500 $500-999 1000 $1,000-2,499 2500 $2,500-4,900 5000 $5,000-4,999 10000 $10,000+ Use a VLookup formula in the source data: =VLOOKUP(B2,GroupLU,2) Then, add that field to the pivot table's row area. To show the years, add the Date field to the Row area, and group by Year: http://www.contextures.com/xlPivot07.html Then, move the grouped date field to the column area. Captain Snuggles wrote: Range 2005 2006 2007 0-$99 $500.00 $200.00 $2,500.00 $100-249 $2,500.00 $500.00 $5,000.00 $250-499 $6,000.00 $900.00 $2,500.00 $500-999 $1,000.00 $2,500.00 $5,000.00 $1,000-2,499 $25,000.00 $16,000.00 $2,500.00 $2,500-4,900 $60,000.00 $50,000.00 $30,000.00 $5,000-4,999 $90,000.00 $70,000.00 $50,000.00 $10,000 + $80,000.00 $70,000.00 $100,500.00 I have a spreadsheet full of individual financial transactions from 2005 to 2007, Amount and Date. I need the columns to be the sum of the values per year and the rows to be the various financial levels. I'm not sure how to group the transactions within the monetary ranges. Any help would be appreciated. -- Debra Dalgleish Contextureshttp://www.contextures.com/tiptech.html Debra, Thanks for your answer. Unfortunately, I have no clue what I'm supposed to do. I can't find anything on creating a "lookup table." I added the Amt and Group columns to the data source, but I can't figure out how to use this info for the grouping. I went to the site mentioned above, but the only grouping it mentioned was grouping pivot table data. I need to group the data in the data source first, I think. Can you think of any more resources I can look at to figure this out? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot table based on amount ranges.
Create a separate table with just the Amt and Group columns as shown
below. This table can be on its own worksheet. This is the lookup table. Name this table GroupLU, as described he http://www.contextures.com/xlNames01.html Next, create a custom list with the grouping categories -- Select the items in the Group column, from the $0-$99 cell to the $10,000+ cell. Choose ToolsOptions, and click the Custom Lists tab. Click Import, then click OK. Add a new column to the source data table, with the column heading AmtGroups. In that column, in row 2, enter the Vlookup formula: =VLOOKUP(B2,GroupLU,2) Change the B2 to match the column in which the Amounts are stored in the source data table. Refresh the pivot table, and add the AmtGroups column to the row area. Captain Snuggles wrote: On Oct 19, 2:16 pm, Debra Dalgleish wrote: You could create a lookup table to group the amounts in the source data table. For example with a named range GroupLU: Amt Group 0 $0-$99 100 $100-249 250 $250-499 500 $500-999 1000 $1,000-2,499 2500 $2,500-4,900 5000 $5,000-4,999 10000 $10,000+ Use a VLookup formula in the source data: =VLOOKUP(B2,GroupLU,2) Then, add that field to the pivot table's row area. To show the years, add the Date field to the Row area, and group by Year: http://www.contextures.com/xlPivot07.html Then, move the grouped date field to the column area. Captain Snuggles wrote: Range 2005 2006 2007 0-$99 $500.00 $200.00 $2,500.00 $100-249 $2,500.00 $500.00 $5,000.00 $250-499 $6,000.00 $900.00 $2,500.00 $500-999 $1,000.00 $2,500.00 $5,000.00 $1,000-2,499 $25,000.00 $16,000.00 $2,500.00 $2,500-4,900 $60,000.00 $50,000.00 $30,000.00 $5,000-4,999 $90,000.00 $70,000.00 $50,000.00 $10,000 + $80,000.00 $70,000.00 $100,500.00 I have a spreadsheet full of individual financial transactions from 2005 to 2007, Amount and Date. I need the columns to be the sum of the values per year and the rows to be the various financial levels. I'm not sure how to group the transactions within the monetary ranges. Any help would be appreciated. -- Debra Dalgleish Contextureshttp://www.contextures.com/tiptech.html Debra, Thanks for your answer. Unfortunately, I have no clue what I'm supposed to do. I can't find anything on creating a "lookup table." I added the Amt and Group columns to the data source, but I can't figure out how to use this info for the grouping. I went to the site mentioned above, but the only grouping it mentioned was grouping pivot table data. I need to group the data in the data source first, I think. Can you think of any more resources I can look at to figure this out? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot table based on amount ranges.
On Oct 24, 5:05 pm, Debra Dalgleish wrote:
Create a separate table with just the Amt and Group columns as shown below. This table can be on its own worksheet. This is the lookup table. Name this table GroupLU, as described he http://www.contextures.com/xlNames01.html Next, create a custom list with the grouping categories -- Select the items in the Group column, from the $0-$99 cell to the $10,000+ cell. Choose ToolsOptions, and click the Custom Lists tab. Click Import, then click OK. Add a new column to the source data table, with the column heading AmtGroups. In that column, in row 2, enter the Vlookup formula: =VLOOKUP(B2,GroupLU,2) Change the B2 to match the column in which the Amounts are stored in the source data table. Refresh the pivot table, and add the AmtGroups column to the row area. Captain Snuggles wrote: On Oct 19, 2:16 pm, Debra Dalgleish wrote: You could create a lookup table to group the amounts in the source data table. For example with a named range GroupLU: Amt Group 0 $0-$99 100 $100-249 250 $250-499 500 $500-999 1000 $1,000-2,499 2500 $2,500-4,900 5000 $5,000-4,999 10000 $10,000+ Use a VLookup formula in the source data: =VLOOKUP(B2,GroupLU,2) Then, add that field to the pivot table's row area. To show the years, add the Date field to the Row area, and group by Year: http://www.contextures.com/xlPivot07.html Then, move the grouped date field to the column area. Captain Snuggles wrote: Range 2005 2006 2007 0-$99 $500.00 $200.00 $2,500.00 $100-249 $2,500.00 $500.00 $5,000.00 $250-499 $6,000.00 $900.00 $2,500.00 $500-999 $1,000.00 $2,500.00 $5,000.00 $1,000-2,499 $25,000.00 $16,000.00 $2,500.00 $2,500-4,900 $60,000.00 $50,000.00 $30,000.00 $5,000-4,999 $90,000.00 $70,000.00 $50,000.00 $10,000 + $80,000.00 $70,000.00 $100,500.00 I have a spreadsheet full of individual financial transactions from 2005 to 2007, Amount and Date. I need the columns to be the sum of the values per year and the rows to be the various financial levels. I'm not sure how to group the transactions within the monetary ranges. Any help would be appreciated. -- Debra Dalgleish Contextureshttp://www.contextures.com/tiptech.html Debra, Thanks for your answer. Unfortunately, I have no clue what I'm supposed to do. I can't find anything on creating a "lookup table." I added the Amt and Group columns to the data source, but I can't figure out how to use this info for the grouping. I went to the site mentioned above, but the only grouping it mentioned was grouping pivot table data. I need to group the data in the data source first, I think. Can you think of any more resources I can look at to figure this out? -- Debra Dalgleish Contextureshttp://www.contextures.com/tiptech.html That was great. Thanks a lot. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating total amount in a Pivot Table | Excel Discussion (Misc queries) | |||
Printing a certain amount of pages based on amount of data | Excel Programming | |||
How can I calculate amount of time left based on amount spent? | Excel Worksheet Functions | |||
Pivot table hidding zeros in sum of amount | Excel Discussion (Misc queries) | |||
Pivot table hidding zeros in sum of amount | Excel Discussion (Misc queries) |