banding by value

[ajm]

I need a formula to label entries by where they sit in some groups. I am working between the raw data and a pivot table which summarises the raw data. The pivot is summed by Supplier and sorted in descending order. against each entry in the pivot table, i wish to create a formula that will identify the band in which a particular supplier sits. Using a nested If statement, I have managed to get the first two spend bands in. The last is not so cooperative.

So, the bands are spend between $100k and $200k, spend greater than $200K up to the next group which is our Top 100 suppliers. The result of the formula will be a label against each of the pivot table entries of either Top100, 200, or 100. We are not looking below the $100k mark in this exercise.

the formula at present is:

IF(C10<$H$1,"",IF(AND(C10>$H$1,C10<$H$2),"100",IF(AND(C10>$H$2,C10<$H$3),"200",IF(C10>$H$3,"Top100",""))))

where:

Column c is the Total spend per supplier
H1 =100000
H2 = 200000
H3 = the cutoff $$value for our top100 (I used a hard coded number to see if my formula would work).

if C10 (first company's expenditure) is less than $100k, not interested
if C10 is between $100k and $200k, label is "100"
if C10 is between $200k and the lowest Top100 value, label is "200"
lastly, if C10 is greater than the Top100 cutoff, label is "Top100"

i can't seem to get Rank to work in the pivot itself. I get "1" returned against each row.

very confused at this point and would appreciate some help.

 

[Cubist]

Try:

Book3
	C	D	E	F	G	H	I	J
1						100000	100
2						200000	200
3						500000	Top100
4
5
6
7
8
9
10	90000
11	100000	100
12	150000	100
13	200000	200
14	250000	200
15	499999	200
16	500000	Top100
17	550000	Top100
Sheet2

Cell Formulas
Range	Formula
D10:D17	D10	=XLOOKUP(C10:C17,H1:H3,I1:I3,"",-1)
Dynamic array formulas.

 

[ajm]

Thanks. The trouble i am having is with the Top100. As you have above i set a number for the cutoff for Top100. I need this to be dynamic. I would like the formula to determine whether or not a supplier is one of the Top100 based on the level of expenditure.

An alternative method might be to add a ranking column to my pivot table and then label the Top100 and exclude the tail first, then band the rest. Ranking doesnt seem t work for me either.

 

[Cubist]

Try:

Book3
	C	D	E	F	G	H	I
1						100,000	100
2						200,000	200
3						475,794	Top100
4
5
6
7
8
9
10	638,004	Top100
11	844,797	Top100
12	811,113	Top100
13	661,407	Top100
14	529,286	Top100
15	71,855
16	73,059
17	992,668	Top100
18	537,429	Top100
19	546,651	Top100
20	469,239	200
21	241,296	200
22	220,767	200
23	366,707	200
24	218,324	200
25	430,164	200
26	406,118	200
27	502,085	Top100
28	968,964	Top100
29	888,695	Top100
30	106,833	100
31	451,892	200
32	286,630	200
33	435,051	200
34	579,912	Top100
35	460,363	200
36	488,668	Top100
37	842,682	Top100
38	873,010	Top100
39	928,661	Top100
40	200,416	200
41	964,787	Top100
42	386,740	200
43	756,164	Top100
44	679,091	Top100
45	310,369	200
46	785,657	Top100
47	682,724	Top100
48	476,556	Top100
49	898,147	Top100
50	439,403	200
51	774,533	Top100
52	876,348	Top100
53	110,499	100
54	163,078	100
55	16,606
56	111,727	100
57	807,122	Top100
58	169,545	100
59	840,935	Top100
60	79,972
61	763,353	Top100
62	985,593	Top100
63	359,209	200
64	175,021	100
65	261,526	200
66	932,608	Top100
67	394,868	200
68	86,325
69	52,719
70	589,689	Top100
71	475,794	Top100
72	796,304	Top100
73	96,991
74	218,649	200
75	401,171	200
76	915,689	Top100
77	476,615	Top100
78	728,901	Top100
79	3,351
80	386,661	200
81	849,416	Top100
82	748,022	Top100
83	762,675	Top100
84	347,373	200
85	569,800	Top100
86	226,176	200
87	93,153
88	989,243	Top100
89	774,862	Top100
90	577,599	Top100
91	338,366	200
92	552,117	Top100
93	395,162	200
94	11,910
95	739,756	Top100
96	647,930	Top100
97	567,345	Top100
98	313,547	200
99	127,926	100
100	736,388	Top100
101	535,703	Top100
102	140,648	100
103	256,725	200
104	351,668	200
105	675,519	Top100
106	640,100	Top100
107	499,204	Top100
108	200,686	200
109	783,381	Top100
110	924,755	Top100
111	208,094	200
112	157,166	100
113	583,767	Top100
114	496,448	Top100
115	284,990	200
116	602,331	Top100
117	299,175	200
118	293,350	200
119	847,155	Top100
120	973,463	Top100
121	539,662	Top100
122	279,516	200
123	843,604	Top100
124	427,977	200
125	206,094	200
126	465,469	200
127	889,576	Top100
128	18,636
129	513,377	Top100
130	18,673
131	177,424	100
132	196,499	100
133	761,798	Top100
134	564,248	Top100
135	884,632	Top100
136	205,420	200
137	861,916	Top100
138	432,338	200
139	952,368	Top100
140	914,889	Top100
141	461,783	200
142	371,662	200
143	142,320	100
144	730,548	Top100
145	176,268	100
146	814,184	Top100
147	840,654	Top100
148	124,525	100
149	353,550	200
150	813,632	Top100
151	188,777	100
152	833,810	Top100
153	460,923	200
154	86,071
155	540,704	Top100
156	84,789
157	2,997
158	519,292	Top100
159	711,552	Top100
160	653,318	Top100
161	45,859
162	116,371	100
163	830,087	Top100
164	178,044	100
165	597,659	Top100
166	640,348	Top100
167	69,592
168	443,921	200
169	583,125	Top100
170	200,140	200
171	730,483	Top100
172	325,196	200
173	550,416	Top100
174	237,686	200
175	178,431	100
176	364,384	200
177	858,704	Top100
178	766,211	Top100
179	140,903	100
180	664,925	Top100
181	129,393	100
182	90,931
183	977,022	Top100
184	656,579	Top100
185	39,645
186	375,363	200
187	215,320	200
188	870,505	Top100
189	888,209	Top100
190	346,219	200
191	338,571	200
192	71,023
193	993,064	Top100
194	717,262	Top100
195	314,489	200
196	993,226	Top100
197	532,604	Top100
198	366,233	200
199	659,723	Top100
200	554,543	Top100
201	912,597	Top100
202	430,586	200
203	594,839	Top100
204	205,939	200
205	235,395	200
206	312,793	200
207	289,473	200
208	248,710	200
209	718,217	Top100
Sheet2

Cell Formulas
Range	Formula
H3	H3	=LARGE(C10:C209,100)
D10:D209	D10	=XLOOKUP(C10:C209,H1:H3,I1:I3,"",-1)
Dynamic array formulas.

 

[ajm]

thanks. hadn't thought of large. will give it a crack.