Bucketing otherwise than by constructing pivottable on pivot

Joel Horowitz

New Member
I'd like some advise on how to better do something: I have sales per product per customer, and I want to have a quick breakdown of sales by

Largest 5 customers
6-10th customers
11-15th customer
...

The way is do it is to construct a pivottable on my database, showing sales by customer. Then I add a column next to the pivottable, that calculates the rank of each customer.

Then I construct a pivottable on this 3-column table, in which I put 'rank' as the row field, 'sales' as the data field, and I use the 'group' function of the pivottable to group it by buckets of 5.

If there is no way to do that in one step, are there pivot-table-like modules for Excel that could have those functions (or any other)?

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Hi Joel - welcome to the board.

One way might be to use the percentrank funciton.

For the series

{1;2;3;4;5;6;7;8;9;10}

entered in A1:A10,

the following array formula will return 40:

=SUM(IF(PERCENTRANK(a1:a10,a1:a10)>0.5,a1:a10))

and

=SUM(IF(PERCENTRANK(a1:a10,a1:a10)>0.8,a1:a10))

will return 19.

(To enter an array formula, use control + shift + enter, not just enter - excel will add curly brackets round it if OK)

An equivalent 'normally entered' formula would be:

=SUMPRODUCT((PERCENTRANK(A:A0,A:A0)>0.8 )*(A1:A10))

Obviously, this depends on you being able to substitute 'Top 5' etc for a percentage - straightforward if you know how many records you're dealing with.

Hope this helps.
This message was edited by PaddyD on 2002-08-24 07:36

I sent a message but it disappeared.

My first thought was to just assemble the information from the list of totals by customer.

=SUM(LARGE(rTot_Cust,{1,2,3,4,5}))

N.B. I use named ranges since I do not know the location of your data.

My second thought was to modify the information in the rank column. Created a lookup table that assigns numbers 1-5 to 1
2-10 as 2 and 11-15 as 3

The formula could then be

=LOOKUP(RANK(B2,rTot_Cust),rGroup)

or without names or separate range for Lookup

=LOOKUP(RANK(B2,\$B\$2:\$B\$25),{0,6,11;1,2,3})

Prepare your pivot table and assemble for your requirements; hide information that you do not require.

You could also look at calculated fields.

HTH
This message was edited by Dave Patton on 2002-08-24 09:45
This message was edited by Dave Patton on 2002-08-24 16:50

Thanks a lot! I am always amazed at the virtuosity of array power-users! I think I will spend some time thinking about all the possible applications of arrays in simplifying my spreadsheets. If anyone has a good list of problems that can be solved by using arrays, please tell me

Hi Joel - welcome to the board.

One way might be to use the percentrank funciton.

For the series

{1;2;3;4;5;6;7;8;9;10}

entered in A1:A10,

the following array formula will return 40:

=SUM(IF(PERCENTRANK(a1:a10,a1:a10)>0.5,a1:a10))

and

=SUM(IF(PERCENTRANK(a1:a10,a1:a10)>0.8,a1:a10))

will return 19.

(To enter an array formula, use control + shift + enter, not just enter - excel will add curly brackets round it if OK)

An equivalent 'normally entered' formula would be:

=SUMPRODUCT((PERCENTRANK(A:A0,A:A0)>0.8 )*(A1:A10))

Obviously, this depends on you being able to substitute 'Top 5' etc for a percentage - straightforward if you know how many records you're dealing with.

Hope this helps.
This message was edited by PaddyD on 2002-08-24 07:36

Replies
0
Views
591
Replies
1
Views
346
Replies
9
Views
382
Replies
0
Views
292
Replies
0
Views
254

1,203,642
Messages
6,056,500
Members
444,872
Latest member
Vishal Gupta

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back