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

helloelucedifel

New Member
Joined
May 17, 2023
Messages
1
Office Version
  1. 2021
Platform
  1. Windows
Hi,

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

DaysRange
-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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Welcome to the Board!

For a value in cell A1, try this formula:
Excel 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",""})
 
Upvote 0
Solution
Format a table with descriptions. Someone will have you change it as soon as its built in my experience.
Excel tricks and testing.xlsx
ABCDEF
1DaysRangeFormula ResultMax DaysRange
2-10BeforeBefore-1Before
3-8BeforeBefore150 - 15 Days
4-7BeforeBefore3016 - 30 Days
5-6BeforeBefore6031 - 60 Days
6-5BeforeBefore10061 - 100 Days
7-2BeforeBefore200101- 200 Days
8-1BeforeBefore
900 - 15 Days0 - 15 Days
1010 - 15 Days0 - 15 Days
1120 - 15 Days0 - 15 Days
1230 - 15 Days0 - 15 Days
1340 - 15 Days0 - 15 Days
1450 - 15 Days0 - 15 Days
1560 - 15 Days0 - 15 Days
1670 - 15 Days0 - 15 Days
1780 - 15 Days0 - 15 Days
1890 - 15 Days0 - 15 Days
19100 - 15 Days0 - 15 Days
20110 - 15 Days0 - 15 Days
21120 - 15 Days0 - 15 Days
22130 - 15 Days0 - 15 Days
23140 - 15 Days0 - 15 Days
24150 - 15 Days0 - 15 Days
251616 - 30 Days16 - 30 Days
262116 - 30 Days16 - 30 Days
272216 - 30 Days16 - 30 Days
282416 - 30 Days16 - 30 Days
292916 - 30 Days16 - 30 Days
303016 - 30 Days16 - 30 Days
313631 - 60 Days31 - 60 Days
323731 - 60 Days31 - 60 Days
334431 - 60 Days31 - 60 Days
344631 - 60 Days31 - 60 Days
354931 - 60 Days31 - 60 Days
365031 - 60 Days31 - 60 Days
375131 - 60 Days31 - 60 Days
385231 - 60 Days31 - 60 Days
395431 - 60 Days31 - 60 Days
406361 - 100 Days61 - 100 Days
417161 - 100 Days61 - 100 Days
427461 - 100 Days61 - 100 Days
437561 - 100 Days61 - 100 Days
447761 - 100 Days61 - 100 Days
458361 - 100 Days61 - 100 Days
468461 - 100 Days61 - 100 Days
478661 - 100 Days61 - 100 Days
48102101- 200 Days101- 200 Days
49111101- 200 Days101- 200 Days
50115101- 200 Days101- 200 Days
51118101- 200 Days101- 200 Days
52146101- 200 Days101- 200 Days
53155101- 200 Days101- 200 Days
54156101- 200 Days101- 200 Days
55172101- 200 Days101- 200 Days
Sheet8
Cell Formulas
RangeFormula
C2:C55C2=XLOOKUP(A2,Table2[Max Days],Table2[Range],,1)
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top