Pivot table based on amount ranges.

[Captain Snuggles]

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]

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]

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.

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]

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 here:
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 Tools>Options, 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]

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 here:
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 Tools>Options, 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.

That was great. Thanks a lot.