make numbers in range into categories

[algl05ab]

Hi all,

I have a challenge which I hope you can help me with.

I have a file with lots of sales per productID and I need to group them into the following:

1-9 sales = S
10-49 = M
50-249 = L
> 249 = XL

It's in the excel file and the fomula in the yellow column should be based on whatever i type in G15:H18. This also means I might add one extra field and call this XXL, XS or the like.

I hope you can help.

thanks!

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[algl05ab]

Hi Ali,

is it better now?

I don't really know how to write a good headliner...

br, Alexander

[AliGW]

Well, you do, because you just have. Thanks!

[AliGW]

Lay out the lookup table thus:

Excel 2016 (Windows) 32 bit

	G	H
14	Sales ranges by Company
15	1	S
16	10	M
17	50	L
18	250	XL

Sheet: Page1_1

Then, in C2 copied down:

=VLOOKUP(B2,$G$15:$H$30,2,1)

[Bo_Ry]

C2
=lookup(b2,{0,1,10,50,250},{"";"S";"M";"L";"XL"})

[algl05ab]

thanks for assistance so far. I have attached another file which perhaps better explains my challenge.

So the categorization must be build on the sales volume that is in the Min and Max matrix. For instance is sales volume = 5 then it should be S, if 47 then M, if 76 then L and 1000 then XL. It should kinda be able to self-determine the category based on the min and max range i have defined (which i might change up- or downwards.

thx

[AliGW]

That's exactly what my solution does. Try changing some of the numbers in your table in column B to test it. Did you even try it???

Look:

Excel 2016 (Windows) 32 bit

	A	B	C
1	Product	Sales	Range (S, M, L, XL)
2	1000	0	#N/A
3	1001	3	S
4	1002	9	S
5	1003	10	M
6	1004	35	M
7	1005	50	L
8	1006	234	L
9	1007	110	L
10	1008	267	XL
11	1009	10,000	XL

Sheet: Page1_1

[algl05ab]

works great! thanks!!!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.