Bucketing otherwise than by constructing pivottable on pivot

[Joel Horowitz]

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)?

 

[PaddyD]

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

 

[Dave Patton]

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
(adj ranges as necessary)

=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

 

[Joel Horowitz]

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

On 2002-08-24 07:35, PaddyD wrote:
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

 

[PaddyD]

HI,

this may or may not help!

http://www.mrexcel.com/tip011.shtml

Paddy