PivotTable: Customized Calculations

J

Jim

I have salary data in an EXCEL spreadsheet (each row
contains the salary for one individual). I wish to
construct a PivotTable that counts the number of
individuals in each of several salary intervals that I
would specify ($0 - $19,999; $20,000 - $29,000, etc.) How
do I do this? I believe that reporting on such customized
calculations are possible, but I can't find any explicit
guidance. Thanks for your help.
 
D

Debra Dalgleish

You could add a column to the data table, and calculate the salary
groupings there.

Create a lookup table with the categories, e.g.:

0 $0-$19,999
20000 $20-$29,000
30000 $30,000 +

In the data table, use a VLOOKUP formula:

=VLOOKUP(D2,SalaryCat,2)

where the salary is in cell D2, and the salary category lookup table is
named SalaryCat

Include this field in the PivotTable, and the records will be grouped by
salary category.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

maximum consecutive values formula 5
Validation 1
Converting multiple rows of Excel data into Word Document 1
lookup source can vary -- how to code? 4
Using check boxes to include/exclude data in calculations 6
PivotTable Calculations 12
Federal tax withholding calculations - using IF statements..? 12
How do I count unique items in a pivottable data field. 2

Top