Categorize by percentage add up to 80%(15%+65%) and 20%(14%+6%) of sales

[hpares]

Hi,
Need help to create a code for :
Item | price | sales qty | percentage of total | formula i need to generate to fill in cell(text)
A 100 2 0.8% RED
A 200 11 7% YELLOW
A 300 50 30% GREEN
A 400 1 0.6% RED
A 500 30 18% LIGHT GREEN
A 600 70 42% GREEN
total sales 164
b 100 2 0.8% RED
b 200 11 7% YELLOW
b 300 50 30% GREEN
b 400 1 0.6% RED
b 500 30 18% LIGHT GREEN
b 600 70 42% GREEN
total sales 164 ( just assume a and B have different sales qty, i just need the percentage)

To determine the color. good sales consist of 80% of sales ( 65% of total 80% will be green, 15% of total 80% will be light green) and 20% of sales will be bad sales ( 14% will be bad and 6% will be terrible)
Rank 1 to 6 ( since got 6 price , but my actually work might have more price, and more item type as well and i need to give each type of item individual 100% (80%good sales and 20% bad sales). So basically, if rank 1 is 42%( not yet reach 65%) it is Green. Even it it is exceed 65%, it is still green because it is rank 1.
For rank 2, depends on rank 1 percentage, it might be green or light green. Since rank 1 not yet fully occupy 65%, in this event, rank 2 also is consider green. But if rank 1 exceed 65% and below 80%, rank 2 have to be light green. For rank 3, if rank 1 + rank 2 > 65%, it have to be light green, but if rank 1 + rank 2 < 65%, rank 3 also will be consider green. But if rank 1, or rank 1 + rank 2 > 80%, rank 3 will no longer become light green, it will become yellow ( since the remaining 20% will be categorize as bad or terrible sales). The last rank will always be red.
To do ranking for each item type, i manage to find a video to do ranking . But i was stuck after that. Thanks.

 

[jasonb75]

The best way that I can think of to do this is to rank with a cumulative percentage in an empty column, then use that as the foundation for a set of conditional formatting rules.

Book12345.xlsm
	A	B	C	D	E	F
1	Item	price	sales qty	percentage of total	Running Total
2	A	100	2	1.22%	1.83%	RED
3	A	200	11	6.71%	8.54%	YELLOW
4	A	300	50	30.49%	57.32%	GREEN
5	A	400	1	0.61%	0.61%	RED
6	A	500	30	18.29%	26.83%	LIGHT GREEN
7	A	600	70	42.68%	100.00%	GREEN
8	total	sales	164
9	b	100	2	1.22%	1.83%	RED
10	b	200	11	6.71%	8.54%	YELLOW
11	b	300	50	30.49%	57.32%	GREEN
12	b	400	1	0.61%	0.61%	RED
13	b	500	30	18.29%	26.83%	LIGHT GREEN
14	b	600	70	42.68%	100.00%	GREEN
Sheet13

Cell Formulas
Range	Formula
D9:D14,D2:D7	D2	=C2*(1/SUMIF(A:A,A2,C:C))
E9:E14,E2:E7	E2	=SUMIFS(D:D,A:A,A2,D:D,"<="&D2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:D7,A9:D14	Expression	=$E2<=6%	text	YES
A2:D7,A9:D14	Expression	=$E2<=20%	text	YES
A2:D7,A9:D14	Expression	=$E2<=35%	text	YES
A2:D7,A9:D14	Expression	=$E2<=100%	text	NO

 

[hpares]

Thanks alot, it solve the problem.