Nested IF statement that considers negative and positive numbers for Range Column

[helloelucedifel]

Hi,

I have calculated column which provides number of days , i need to create range

Days	Range
-10​	Before
-8​	Before
-7​	Before
-6​	Before
-3​	Before
-2​	Before
-1​	Before
0​	0 - 15 Days
1​	0 - 15 Days
2​	0 - 15 Days
3​	0 - 15 Days
4​	0 - 15 Days
5​	0 - 15 Days
6​	0 - 15 Days
7​	0 - 15 Days
8​	0 - 15 Days
9​	0 - 15 Days
10​	0 - 15 Days
11​	0 - 15 Days
12​	0 - 15 Days
13​	0 - 15 Days
14​	0 - 15 Days
15​	0 - 15 Days
16​	16 - 30 Days
21​	16 - 30 Days
22​	16 - 30 Days
24​	16 - 30 Days
29​	16 - 30 Days
30​	16 - 30 Days
36​	31 - 60 Days
37​	31 - 60 Days
44​	31 - 60 Days
46​	31 - 60 Days
49​	31 - 60 Days
50​	31 - 60 Days
51​	31 - 60 Days
52​	31 - 60 Days
54​	31 - 60 Days
63​	61 - 100 Days
71​	61 - 100 Days
74​	61 - 100 Days
75​	61 - 100 Days
77​	61 - 100 Days
83​	61 - 100 Days
84​	61 - 100 Days
86​	61 - 100 Days
102​	101- 200 Days
111​	101- 200 Days
115​	101- 200 Days
118​	101- 200 Days
146​	101- 200 Days
155​	101- 200 Days
156​	101- 200 Days
172​	101- 200 Days

 

[Joe4]

Welcome to the Board!

For a value in cell A1, try this formula:

=LOOKUP(A1,{-10,0,16,31,61,101,201},{"Before","0 - 15 Days","16 - 30 Days","31 - 60 Days","61 - 100 Days","101 - 200 Days",""})

 

[C Moore]

Format a table with descriptions. Someone will have you change it as soon as its built in my experience.

Excel tricks and testing.xlsx
	A	B	C	D	E	F
1	Days	Range	Formula Result		Max Days	Range
2	-10	Before	Before		-1	Before
3	-8	Before	Before		15	0 - 15 Days
4	-7	Before	Before		30	16 - 30 Days
5	-6	Before	Before		60	31 - 60 Days
6	-5	Before	Before		100	61 - 100 Days
7	-2	Before	Before		200	101- 200 Days
8	-1	Before	Before
9	0	0 - 15 Days	0 - 15 Days
10	1	0 - 15 Days	0 - 15 Days
11	2	0 - 15 Days	0 - 15 Days
12	3	0 - 15 Days	0 - 15 Days
13	4	0 - 15 Days	0 - 15 Days
14	5	0 - 15 Days	0 - 15 Days
15	6	0 - 15 Days	0 - 15 Days
16	7	0 - 15 Days	0 - 15 Days
17	8	0 - 15 Days	0 - 15 Days
18	9	0 - 15 Days	0 - 15 Days
19	10	0 - 15 Days	0 - 15 Days
20	11	0 - 15 Days	0 - 15 Days
21	12	0 - 15 Days	0 - 15 Days
22	13	0 - 15 Days	0 - 15 Days
23	14	0 - 15 Days	0 - 15 Days
24	15	0 - 15 Days	0 - 15 Days
25	16	16 - 30 Days	16 - 30 Days
26	21	16 - 30 Days	16 - 30 Days
27	22	16 - 30 Days	16 - 30 Days
28	24	16 - 30 Days	16 - 30 Days
29	29	16 - 30 Days	16 - 30 Days
30	30	16 - 30 Days	16 - 30 Days
31	36	31 - 60 Days	31 - 60 Days
32	37	31 - 60 Days	31 - 60 Days
33	44	31 - 60 Days	31 - 60 Days
34	46	31 - 60 Days	31 - 60 Days
35	49	31 - 60 Days	31 - 60 Days
36	50	31 - 60 Days	31 - 60 Days
37	51	31 - 60 Days	31 - 60 Days
38	52	31 - 60 Days	31 - 60 Days
39	54	31 - 60 Days	31 - 60 Days
40	63	61 - 100 Days	61 - 100 Days
41	71	61 - 100 Days	61 - 100 Days
42	74	61 - 100 Days	61 - 100 Days
43	75	61 - 100 Days	61 - 100 Days
44	77	61 - 100 Days	61 - 100 Days
45	83	61 - 100 Days	61 - 100 Days
46	84	61 - 100 Days	61 - 100 Days
47	86	61 - 100 Days	61 - 100 Days
48	102	101- 200 Days	101- 200 Days
49	111	101- 200 Days	101- 200 Days
50	115	101- 200 Days	101- 200 Days
51	118	101- 200 Days	101- 200 Days
52	146	101- 200 Days	101- 200 Days
53	155	101- 200 Days	101- 200 Days
54	156	101- 200 Days	101- 200 Days
55	172	101- 200 Days	101- 200 Days
Sheet8

Cell Formulas
Range	Formula
C2:C55	C2	=XLOOKUP(A2,Table2[Max Days],Table2[Range],,1)