Formatting matrix

[Pruitel]

I have a matrix that has Revenue value per Customer (thousands) and per Product Group (only 10). I want to highlight the top 80% of total value items per Product Group. I tried using Conditional Formatting, but that does not seem to work out well since it does not calculate to the total of each column.


Grp 1 Grp 2 Grp 3

Cust A 34 5

Cust B 66 6 6

Cust C 5 7 7

..

Cust AA 34 5

Cust ZZ 5 7 35

 

[Hui]

Pruitel


Firstly, Welcome to the Chandoo.org Forums


Have you tried selecting each column by itself and using Cconditional formatting

Top/Bottom Rules

Top10%

and change the 10% to 80%


Doesn't this do what you want?

 

[Pruitel]

Hui, thanks for your response/welcome.

I have tried the selection of individual columns and changed the 10 to 80%, but that is not driven by the value inside the cells, seems only be driven by the number of cells.

Maybe the below will explain. 10 entries with values. Col Form80% will highlight 80% of cells, yes the 8 out of 10 highest values (only the 1 and 0 will not highlight) but not to the 80% from the total of the column. My intention is to highlight 12, 9, 8, 6 and 5 from the Random column. That is why I used a side calculation: ranked them hi-lo calculated individual contribution and total contribution and marked 80%, but in a matrix this is too complex and it is not the only calculation I need in this sheet.


Entry Form80% Random Hi-Lo %tot cum totl

1 2 2 12 24% 24%

2 3 3 9 18% 42%

3 9 9 8 16% 58%

4 8 8 6 12% 70%

5 1 1 5 10% 80%

6 5 5 4 8% 88%

7 12 12 3 6% 94%

8 4 4 2 4% 98%

9 0 0 1 2% 100%

10 6 6 0 0% 100%

Sum 50 50 50


Any help is much appreciated.

 

[Hui]

If you want to highlight the top cells that make up 80% of the total,

I'd use helper columns

But I'll keep thinking about it

 

[Pruitel]

Maybe the link to the real file will help a bit, ...


https://docs.google.com/spreadsheet/ccc?key=0AsEJKqCIvfZTdGJEQTNCSGVtRVB2bThsU1QyOFNJNXc&hl=nl