Help~ Ranking (Multiple conditions) - Missing Ranking Numbers >_<

[Chanzuihou]

Hi all,

I am building up a forecast model, which will need 2 Rankings:
1) for Each Customer, Rank each Product Category sales total
2) for Each Customer, and its Each Product Category, Rank each Items' sales total

I managed to have 2 ranking formula working, BUT have a couple of problems
- For customer# 4013 (same for #4373), why for it's Product Category's sales total, Ranking starts from 0? How can I get the formula to rank from 1? Some of my other customers Product Category all starts from 1?

- For customer #4373, and for its Product #210, why ranking #8 is missing? How can the formula of Ranking NOT skip a number?

Thanking for everyone who can help in advance, sincerely.

Excel 2012
	A	B	C	D	E	F	G	H
1	Item No.	Item Description	Product Category	Customer No.	2016 YTD Sales	PG Subtotal (per Customer)	Ranking - by PG (per Customer)	Ranking - by Item (per Customer, per PG)
2	3310137.000	CAL242, WALL CONTROL	130	4373	0	14,538	2	9
3	40801-02	AIRBOX TRUMA AVENTA MK2 AIRCON	130	4373	199	14,538	2	8
4	44033-02	TRUMA SAPHIR COMFORT RC AIRCON	130	4373	0	14,538	2	9
5	44091-52	TRUMA AVENTA A/C ROOF/SEC MK2	130	4373	1,632	14,538	2	5
6	50500-02	LEVEL CHECK GAS BOTTLE TRUMA	190	4013	4,290	63,182	2	6
7	5750001	CORMORANT MK2 ROOFTOP UNIT	130	4373	0	14,538	2	9
8	8100001	IBIS 3 ROOFTOP UNIT	130	4373	1,440	14,538	2	7
9	AVENTA MARK2-KIT	TRUMA AVENTA MK2 AIRCON + ADB	130	4373	0	14,538	2	9
10	IIIF11	COOLFREEZE 10.5L	330	4013	63,883	485,729	0	2
11	IIIF11	COOLFREEZE 10.5L	330	4373	0	27,632	1	7
12	IIIF35	32.5L CDF35 FRIDGE	330	4373	0	27,632	1	7
13	IIIF35 PWIPK	CDF35 + EPS817 + AC-CB-CDF35	330	4013	57,120	485,729	0	3
14	CF-110WIPVERB	CF-110ACVERB	330	4013	5,264	485,729	0	15
15	CF-110WIPVERB	CF-110ACVERB	330	4373	0	27,632	1	7
16	CF18	CF18 COMPRESSOR FRIDGE	330	4013	35,585	485,729	0	6
17	CF18	CF18 COMPRESSOR FRIDGE	330	4373	508	27,632	1	6
18	CF-25VERA	CF-25VERA	330	4013	9,824	485,729	0	13
19	CF40 PWIPK	CF-40ACVERB + AC-CB-CF40	330	4013	73,206	485,729	0	1
20	CF40 PWIPK	CF-40ACVERB + AC-CB-CF40	330	4373	0	27,632	1	7
21	CF50 PWIPK	CF-50ACVERB + AC-CB-CF50	330	4013	42,864	485,729	0	5
22	CF50 PWIPK	CF-50ACVERB + AC-CB-CF50	330	4373	1,453	27,632	1	5
23	CF-50WIPVERB	CF-50ACVERB	330	4373	0	27,632	1	7
24	CF-80WIPDZVERB	CF-80 Dual Zone	330	4013	0	485,729	0	18
25	CF-80WIPVERB1	CF-80ACVERB1	330	4013	10,530	485,729	0	12
26	CFX-28	FRIDGE/FZR COMP PORTABLE 28.2L	330	4013	6,539	485,729	0	14
27	CFX-28	FRIDGE/FZR COMP PORTABLE 28.2L	330	4373	1,453	27,632	1	4
28	CFX-35	FRIDGE/FZR COMP PORTABLE 34.5L	330	4013	19,220	485,729	0	11
29	CFX-40	FRIDGE/FZR COMP PORTABLE 41L	330	4013	30,884	485,729	0	7
30	CFX-40	FRIDGE/FZR COMP PORTABLE 41L	330	4373	1,817	27,632	1	3
31	CFX40W	FRIDGE OR FZR COMP PORTABLE	330	4013	0	485,729	0	18
32	CFX-50	FRIDGE/FZR COMP PORTABLE 50L	330	4013	20,349	485,729	0	10
33	CFX-50	FRIDGE/FZR COMP PORTABLE 50L	330	4373	3,052	27,632	1	2
34	CFX50W	FRIDGE OR FZR COMP PORTABLE	330	4013	0	485,729	0	18
35	CFX-65	FRIDGE/FZR COMP PORTABLE 65L	330	4013	21,404	485,729	0	9
36	CFX-65	FRIDGE/FZR COMP PORTABLE 65L	330	4373	0	27,632	1	7
37	CFX-65DZ	FRIDGE & FZR COMP PORTABLE 61L	330	4013	53,494	485,729	0	4
38	CFX-65DZ	FRIDGE & FZR COMP PORTABLE 61L	330	4373	0	27,632	1	7
39	CFX-95DZ2	FRIDGE & FZR COMP 2 LID 94.5L	330	4013	30,404	485,729	0	8
40	CFX-95DZ2	FRIDGE & FZR COMP 2 LID 94.5L	330	4373	19,348	27,632	1	1
41	CFX95DZW	FRIDGE & FZR COMP PORTABLE	330	4013	0	485,729	0	18
42	CU84	COOLMATIC COOLING UNIT 84	210	4373	1,458	42,467	0	7
43	CU85	COOLMATIC COOLING UNIT 85	210	4373	2,431	42,467	0	4
44	CU86	COOLMATIC COOLING UNIT 86	210	4373	1,458	42,467	0	7
45	CU94	COOLMATIC COOLING UNIT 94	210	4373	7,298	42,467	0	2
46	CU95	COOLMATIC COOLING UNIT 95	210	4373	15,156	42,467	0	1
47	CU96	COOLMATIC COOLING UNIT 96	210	4373	5,614	42,467	0	3
48	CU97	COOLMATIC COOLING UNIT 97	210	4373	2,218	42,467	0	5
49	FJ2200-ADB-KIT	FJ2200 FRESHJET AIRCON + ADB	130	4373	1,658	14,538	2	4
50	FJ3200-ADB-KIT	FJ3200 FRESHJET AIRCON + ADB	130	4373	3,316	14,538	2	1
51	IBIS3-ADB-KIT	IBIS 3 + ADB	130	4373	0	14,538	2	9
52	IBIS-ADB-KIT	IBIS MK2 + ADB	130	4373	1,550	14,538	2	6
53	M-2073A	GRAND AERO FLAT STD ARM	190	4013	5,600	63,182	2	4
54	M-2899	MILENCO MIRROR AERO WIDE PAIR	190	4013	6,750	63,182	2	3
55	M-2912	MILENCO MIRROR GRAND AERO PAIR	190	4013	27,200	63,182	2	1
56	M-2936	MILENCO LEVEL TRIPLE PAIR	190	4013	2,360	63,182	2	8
57	M-3117	MILENCO LEVEL INDICATOR GAUGE	190	4013	0	63,182	2	10
58	M-3155	MILENCO 400KG NOSE WGHT GAUGE	190	4013	3,540	63,182	2	7
59	M-3896	MILENCO MINI LEVEL T1 PAIR	190	4013	882	63,182	2	9
60	M-3988	MILENCO FALCON MIRROR TWIN PCK	190	4013	4,400	63,182	2	5
61	P-CFX35WINTER	CFX35 WINTER KIT	330	4013	3,343	485,729	0	16
62	P-CFX40WINTER	CFX40 WINTER KIT	330	4013	1,817	485,729	0	17
63	SAPHIR R/C-KIT	TRUMA SAPHIR COMFORT RC AIRCON	130	4373	1,683	14,538	2	3
64	SPARROW-ADB-KIT	SPARROW MK4 + ADB	130	4373	3,060	14,538	2	2
65	VD03	COOLMATIC EVAPORATOR LAME	210	4373	0	42,467	0	15
66	VD05	COOLMATIC EVAPORATOR PLAT	210	4373	0	42,467	0	15
67	VD06	COOLMATIC EVAPORATOR SPEI	210	4373	0	42,467	0	15
68	VD08	COOLMATIC EVAPORATOR WINK	210	4373	342	42,467	0	14
69	VD09	COOLMATIC EVAPORATOR KAST	210	4373	855	42,467	0	12
70	VD14N	COOLMATIC EVAPORATOR UMLU	210	4373	1,344	42,467	0	9
71	VD15	COOLMATIC EVAPORATOR UMLU	210	4373	896	42,467	0	11
72	VD16	COOLMATIC EVAPORATOR UMLU	210	4373	896	42,467	0	10
73	VD18	COOLMATIC EVAPORATOR PLAT	210	4373	779	42,467	0	13
74	VD21	COOLMATIC EVAPORATOR	210	4373	1,722	42,467	0	6
75	VS-11ROLL	11" VACUUM SEALER ROLL TWIN PK	190	4013	0	63,182	2	10
76	VS-8ROLL	8" VACUUM SEALER ROLLS TWIN PK	190	4013	0	63,182	2	10
77	VS-WIP/DC	DOMETIC VACUUM SEALER	190	4013	8,160	63,182	2	2
78	WIBB-110	COOLICE 111L ROTOMOLD ICEBOX	340	4013	5,985	81,272	1	5
79	WIBB-13	COOLICE 13L ROTOMOLD ICEBOX	340	4013	9,409	81,272	1	4
80	WIBB-22	COOLICE 22L ROTOMOLD ICEBOX	340	4013	21,246	81,272	1	1
81	WIBB-33	COOLICE 33L ROTOMOLD ICEBOX	340	4013	16,417	81,272	1	2
82	WIBB-42	COOLICE 41L ROTOMOLD ICEBOX	340	4013	10,313	81,272	1	3
83	WIBB-55	COOLICE 55L ROTOMOLD ICEBOX	340	4013	4,332	81,272	1	6
84	WIBB-70	COOLICE 68L ROTOMOLD ICEBOX	340	4013	4,307	81,272	1	7
85	WIBB-70 BLUE	ICEBOX 68L BLUE	340	4013	1,182	81,272	1	11
86	WIBB-70 RED/MID	ICEBOX 68L CARLTON MID	340	4013	1,051	81,272	1	12
87	WIBB-70 YELLOW	ICEBOX 68L YELLOW	340	4013	2,102	81,272	1	9
88	WIBB-85	COOLICE 87L ROTOMOLD ICEBOX	340	4013	2,085	81,272	1	10
89	WIBB-85W	ICEBOX 86L WHEELS	340	4013	2,844	81,272	1	8
90	Z-M-2615	MILENCO LEVEL QUATTRO PAIR	190	4013	0	63,182	2	10
91	Z-M-2899	MILENCO MIRROR AERO WIDE PAIR	190	4013	0	63,182	2	10
92	Z-M-2912	MILENCO MIRROR GRAND AERO PAIR	190	4013	0	63,182	2	10
93	Z-M-2936	MILENCO LEVEL TRIPLE PAIR	190	4013	0	63,182	2	10
94	Z-M-3896	MILENCO MINI LEVEL T1 PAIR	190	4013	0	63,182	2	10
Sheet1 (2)

 

[Chanzuihou]

BTW, my formula for G2: =SUMPRODUCT(--(IF(FREQUENCY(IF($D$2:$D$1515=$D2,$F$2:$F$1515),IF($D$2:$D$1515=$D2,$F$2:$F$1515,0)),$F$2:$F$1515,-100)>F2))

formula for H2: =SUMPRODUCT(($D$2:$D$56507=$D2)*($C$2:$C$56507=$C2)*($E2<$E$2:$E$56507))+1


Both Ctrl+Shift+Enter

Really appreciate it~~

 

[Chanzuihou]

Can anyone PLEASE help me with this, as I desperately would like to overcome these hurdles, so that we can start accurate forecasting ... ...

thanks in advance

 

[barry houdini]

formula for H2: =SUMPRODUCT(($D$2:$D$56507=$D2)*($C$2:$C$56507=$C2)*($E2<$E$2:$E$56507))+1

Just looking at this formula (it doesn't need array entry), this is a standard approach for ranking with conditions and like RANK function, if there are duplicates (there are two items ranked 7) then there will also be gaps. I'd say that's normal, why do you need to eliminate gaps?

 

[Chanzuihou]

Just looking at this formula (it doesn't need array entry), this is a standard approach for ranking with conditions and like RANK function, if there are duplicates (there are two items ranked 7) then there will also be gaps. I'd say that's normal, why do you need to eliminate gaps?

Thanks barry, the reason I will need to eliminate gaps, is because I have another sheet to show top 10 ranking items. So if there are gaps in between, in this example, ranking number 8 will be blank.

Is there any way to eliminate gaps? Also from your view, how to I need to improve this formula? How to get rid of the array entry?

Thanks again