Chanzuihou
New Member
- Joined
- Jul 28, 2016
- Messages
- 19
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.
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) |