How to use multiple if function on my example

[Joko Purnomo]

Dear all,

Help me, for my dificult, how to use the multiple if formula between both column example A, and column example C, so that the result is in the result example column. Details explanation on column "range A and range B", include illustration formula. I say thank you for helping.

Best Regard,

Range A	Formula Range A	Range B	Formula Range B	Result C	Example A	Example B	Result Example
0	= 0		"" = blank	ICP/RRD/Exp/Inv	0		ICP/RRD/Exp/Inv
0	= 0	0	= 0	Current	0	0	Current
0	= 0	1 - 30	>= 1 and <= 30	Roll to 1 - 30	0	0	Roll to 1 - 30
1 - 30	>= 1 and <= 30		"" = blank	ICP/RRD/Exp/Inv	10		ICP/RRD/Exp/Inv
1 - 30	>= 1 and <= 30	0	= 0	Bact to Current	11	0	Bact to Current
1 - 30	>= 1 and <= 30	1 - 30	>= 1 and <= 30	Same Aging	12	13	Same Aging
1 - 30	>= 1 and <= 30	31 - 60	>= 31 and <= 60	Roll to 30+	13	35	Roll to 30+
31 - 60	>= 31 and <= 60		"" = blank	ICP/RRD/Exp/Inv	55		ICP/RRD/Exp/Inv
31 - 60	>= 31 and <= 60	0	= 0	Bact to Current	56	0	Bact to Current
31 - 60	>= 31 and <= 60	1 - 30	>= 1 and <= 30	Roll Back to 1 - 30	57	11	Roll Back to 1 - 30
31 - 60	>= 31 and <= 60	31 - 60	>= 31 and <= 60	Same Aging	58	57	Same Aging
31 - 60	>= 31 and <= 60	61 - 90	>= 61 and <= 90	Roll to 60+	59	63	Roll to 60+
61 - 90	>= 61 and <= 90		"" = blank	ICP/RRD/Exp/Inv	77		ICP/RRD/Exp/Inv
61 - 90	>= 61 and <= 90	0	= 0	Bact to Current	78	0	Bact to Current
61 - 90	>= 61 and <= 90	1 - 30	>= 1 and <= 30	Roll Back to 1 - 30	79	12	Roll Back to 1 - 30
61 - 90	>= 61 and <= 90	31 - 60	>= 31 and <= 60	Roll Back to 31 - 60	80	31	Roll Back to 31 - 60
61 - 90	>= 61 and <= 90	61 - 90	>= 61 and <= 90	Same Aging	81	86	Same Aging
61 - 90	>= 61 and <= 90	91 - 180	>= 91 and <= 180	Roll to 90+	82	93	Roll to 90+
91 - 180	>= 91 and <= 180		"" = blank	ICP/RRD/Exp/Inv	92		ICP/RRD/Exp/Inv
91 - 180	>= 91 and <= 180	0	= 0	Bact to Current	93	0	Bact to Current
91 - 180	>= 91 and <= 180	1 - 30	>= 1 and <= 30	Roll Back to 1 - 30	94	15	Roll Back to 1 - 30
91 - 180	>= 91 and <= 180	31 - 60	>= 31 and <= 60	Roll Back to 31 - 60	95	36	Roll Back to 31 - 60
91 - 180	>= 91 and <= 180	61 - 90	>= 61 and <= 90	Roll Back to 61 - 90	96	67	Roll Back to 61 - 90
91 - 180	>= 91 and <= 180	91 - 180	>= 91 and <= 180	Same Aging	97	100	Same Aging
91 - 180	>= 91 and <= 180	181 - 210	>= 181 and <= 210	Roll to 180+	98	192	Roll to 180+
181 - 210	>= 181 and <= 210		"" = blank	ICP/RRD/Exp/Inv	185		ICP/RRD/Exp/Inv
181 - 210	>= 181 and <= 210	0	= 0	Bact to Current	186	0	Bact to Current
181 - 210	>= 181 and <= 210	1 - 30	>= 1 and <= 30	Roll Back to 1 - 30	187	17	Roll Back to 1 - 30
181 - 210	>= 181 and <= 210	31 - 60	>= 31 and <= 60	Roll Back to 31 - 60	188	39	Roll Back to 31 - 60
181 - 210	>= 181 and <= 210	61 - 90	>= 61 and <= 90	Roll Back to 61 - 90	189	89	Roll Back to 61 - 90
181 - 210	>= 181 and <= 210	91 - 180	>= 91 and <= 180	Roll Back to 91 - 180	190	98	Roll Back to 91 - 180
181 - 210	>= 181 and <= 210	181 - 210	>= 181 and <= 210	Same Aging	191	200	Same Aging
181 - 210	>= 181 and <= 210	> 210	> 210	Roll to WO	192	215	Roll to WO

 

[jasonb75]

Will this work for you? There appears to be an error in your example as highlighted below.

**Edited reply, formula changed to work with blank or empty cells**

Book1
	A	B	C	D	E	F	G	H	I
1	Start A	End A	Start B	End B	Result C	Example A	Example B	Result Example	Formula result
2	0	0			ICP/RRD/Exp/Inv	0		ICP/RRD/Exp/Inv	ICP/RRD/Exp/Inv
3	0	0	0	0	Current	0	0	Current	Current
4	0	0	1	30	Roll to 1 - 30	0	0	Roll to 1 - 30	Current
5	1	30			ICP/RRD/Exp/Inv	10		ICP/RRD/Exp/Inv	ICP/RRD/Exp/Inv
6	1	30	0	0	Bact to Current	11	0	Bact to Current	Bact to Current
7	1	30	1	30	Same Aging	12	13	Same Aging	Same Aging
8	1	30	31	60	Roll to 30+	13	35	Roll to 30+	Roll to 30+
9	31	60			ICP/RRD/Exp/Inv	55		ICP/RRD/Exp/Inv	ICP/RRD/Exp/Inv
10	31	60	0	0	Bact to Current	56	0	Bact to Current	Bact to Current
11	31	60	1	30	Roll Back to 1 - 30	57	11	Roll Back to 1 - 30	Roll Back to 1 - 30
12	31	60	31	60	Same Aging	58	57	Same Aging	Same Aging
13	31	60	61	90	Roll to 60+	59	63	Roll to 60+	Roll to 60+
14	61	90			ICP/RRD/Exp/Inv	77		ICP/RRD/Exp/Inv	ICP/RRD/Exp/Inv
15	61	90	0	0	Bact to Current	78	0	Bact to Current	Bact to Current
16	61	90	1	30	Roll Back to 1 - 30	79	12	Roll Back to 1 - 30	Roll Back to 1 - 30
17	61	90	31	60	Roll Back to 31 - 60	80	31	Roll Back to 31 - 60	Roll Back to 31 - 60
18	61	90	61	90	Same Aging	81	86	Same Aging	Same Aging
19	61	90	91	180	Roll to 90+	82	93	Roll to 90+	Roll to 90+
20	91	180			ICP/RRD/Exp/Inv	92		ICP/RRD/Exp/Inv	ICP/RRD/Exp/Inv
21	91	180	0	0	Bact to Current	93	0	Bact to Current	Bact to Current
22	91	180	1	30	Roll Back to 1 - 30	94	15	Roll Back to 1 - 30	Roll Back to 1 - 30
23	91	180	31	60	Roll Back to 31 - 60	95	36	Roll Back to 31 - 60	Roll Back to 31 - 60
24	91	180	61	90	Roll Back to 61 - 90	96	67	Roll Back to 61 - 90	Roll Back to 61 - 90
25	91	180	91	180	Same Aging	97	100	Same Aging	Same Aging
26	91	180	181	210	Roll to 180+	98	192	Roll to 180+	Roll to 180+
27	181	210			ICP/RRD/Exp/Inv	185		ICP/RRD/Exp/Inv	ICP/RRD/Exp/Inv
28	181	210	0	0	Bact to Current	186	0	Bact to Current	Bact to Current
29	181	210	1	30	Roll Back to 1 - 30	187	17	Roll Back to 1 - 30	Roll Back to 1 - 30
30	181	210	31	60	Roll Back to 31 - 60	188	39	Roll Back to 31 - 60	Roll Back to 31 - 60
31	181	210	61	90	Roll Back to 61 - 90	189	89	Roll Back to 61 - 90	Roll Back to 61 - 90
32	181	210	91	180	Roll Back to 91 - 180	190	98	Roll Back to 91 - 180	Roll Back to 91 - 180
33	181	210	181	210	Same Aging	191	200	Same Aging	Same Aging
34	181	210	210	1.00E+100	Roll to WO	192	215	Roll to WO	Roll to WO
Sheet5

Cell Formulas
Range	Formula
I2:I34	I2	=LOOKUP(2,1/($A$2:$A$34<=F2)/($B$2:$B$34>=F2)/IF(G2="",$C$2:$C$34="",$C$2:$C$34<=G2)/IF(G2="",$C$2:$C$34="",$D$2:$D$34>=G2),$E$2:$E$34)

 

[Joko Purnomo]

Will this work for you? There appears to be an error in your example as highlighted below.

**Edited reply, formula changed to work with blank or empty cells**

Book1
	A	B	C	D	E	F	G	H	I
1	Start A	End A	Start B	End B	Result C	Example A	Example B	Result Example	Formula result
2	0	0			ICP/RRD/Exp/Inv	0		ICP/RRD/Exp/Inv	ICP/RRD/Exp/Inv
3	0	0	0	0	Current	0	0	Current	Current
4	0	0	1	30	Roll to 1 - 30	0	0	Roll to 1 - 30	Current
5	1	30			ICP/RRD/Exp/Inv	10		ICP/RRD/Exp/Inv	ICP/RRD/Exp/Inv
6	1	30	0	0	Bact to Current	11	0	Bact to Current	Bact to Current
7	1	30	1	30	Same Aging	12	13	Same Aging	Same Aging
8	1	30	31	60	Roll to 30+	13	35	Roll to 30+	Roll to 30+
9	31	60			ICP/RRD/Exp/Inv	55		ICP/RRD/Exp/Inv	ICP/RRD/Exp/Inv
10	31	60	0	0	Bact to Current	56	0	Bact to Current	Bact to Current
11	31	60	1	30	Roll Back to 1 - 30	57	11	Roll Back to 1 - 30	Roll Back to 1 - 30
12	31	60	31	60	Same Aging	58	57	Same Aging	Same Aging
13	31	60	61	90	Roll to 60+	59	63	Roll to 60+	Roll to 60+
14	61	90			ICP/RRD/Exp/Inv	77		ICP/RRD/Exp/Inv	ICP/RRD/Exp/Inv
15	61	90	0	0	Bact to Current	78	0	Bact to Current	Bact to Current
16	61	90	1	30	Roll Back to 1 - 30	79	12	Roll Back to 1 - 30	Roll Back to 1 - 30
17	61	90	31	60	Roll Back to 31 - 60	80	31	Roll Back to 31 - 60	Roll Back to 31 - 60
18	61	90	61	90	Same Aging	81	86	Same Aging	Same Aging
19	61	90	91	180	Roll to 90+	82	93	Roll to 90+	Roll to 90+
20	91	180			ICP/RRD/Exp/Inv	92		ICP/RRD/Exp/Inv	ICP/RRD/Exp/Inv
21	91	180	0	0	Bact to Current	93	0	Bact to Current	Bact to Current
22	91	180	1	30	Roll Back to 1 - 30	94	15	Roll Back to 1 - 30	Roll Back to 1 - 30
23	91	180	31	60	Roll Back to 31 - 60	95	36	Roll Back to 31 - 60	Roll Back to 31 - 60
24	91	180	61	90	Roll Back to 61 - 90	96	67	Roll Back to 61 - 90	Roll Back to 61 - 90
25	91	180	91	180	Same Aging	97	100	Same Aging	Same Aging
26	91	180	181	210	Roll to 180+	98	192	Roll to 180+	Roll to 180+
27	181	210			ICP/RRD/Exp/Inv	185		ICP/RRD/Exp/Inv	ICP/RRD/Exp/Inv
28	181	210	0	0	Bact to Current	186	0	Bact to Current	Bact to Current
29	181	210	1	30	Roll Back to 1 - 30	187	17	Roll Back to 1 - 30	Roll Back to 1 - 30
30	181	210	31	60	Roll Back to 31 - 60	188	39	Roll Back to 31 - 60	Roll Back to 31 - 60
31	181	210	61	90	Roll Back to 61 - 90	189	89	Roll Back to 61 - 90	Roll Back to 61 - 90
32	181	210	91	180	Roll Back to 91 - 180	190	98	Roll Back to 91 - 180	Roll Back to 91 - 180
33	181	210	181	210	Same Aging	191	200	Same Aging	Same Aging
34	181	210	210	1.00E+100	Roll to WO	192	215	Roll to WO	Roll to WO
Sheet5

Cell Formulas
Range	Formula
I2:I34	I2	=LOOKUP(2,1/($A$2:$A$34<=F2)/($B$2:$B$34>=F2)/IF(G2="",$C$2:$C$34="",$C$2:$C$34<=G2)/IF(G2="",$C$2:$C$34="",$D$2:$D$34>=G2),$E$2:$E$34)

I mean like this :

This is only the full classification :

Classification A	Classification B	Result C
0		ICP/RRD/Exp/Inv
0	0	Current
0	1 - 30	Roll to 1 - 30
1 - 30		ICP/RRD/Exp/Inv
1 - 30	0	Bact to Current
1 - 30	1 - 30	Same Aging
1 - 30	31 - 60	Roll to 30+
31 - 60		ICP/RRD/Exp/Inv
31 - 60	0	Bact to Current
31 - 60	1 - 30	Roll Back to 1 - 30
31 - 60	31 - 60	Same Aging
31 - 60	61 - 90	Roll to 60+
61 - 90		ICP/RRD/Exp/Inv
61 - 90	0	Bact to Current
61 - 90	1 - 30	Roll Back to 1 - 30
61 - 90	31 - 60	Roll Back to 31 - 60
61 - 90	61 - 90	Same Aging
61 - 90	91 - 180	Roll to 90+
91 - 180		ICP/RRD/Exp/Inv
91 - 180	0	Bact to Current
91 - 180	1 - 30	Roll Back to 1 - 30
91 - 180	31 - 60	Roll Back to 31 - 60
91 - 180	61 - 90	Roll Back to 61 - 90
91 - 180	91 - 180	Same Aging
91 - 180	181 - 210	Roll to 180+
181 - 210		ICP/RRD/Exp/Inv
181 - 210	0	Bact to Current
181 - 210	1 - 30	Roll Back to 1 - 30
181 - 210	31 - 60	Roll Back to 31 - 60
181 - 210	61 - 90	Roll Back to 61 - 90
181 - 210	91 - 180	Roll Back to 91 - 180
181 - 210	181 - 210	Same Aging
181 - 210	> 210	Roll to WO

And the case i want to know from the classification above :

column A	column B	Result Example
0		ICP/RRD/Exp/Inv
0	0	Current
0	12	Roll to 1 - 30
10		ICP/RRD/Exp/Inv
11	0	Bact to Current
12	13	Same Aging
13	35	Roll to 30+
55		ICP/RRD/Exp/Inv
56	0	Bact to Current
57	11	Roll Back to 1 - 30
58	57	Same Aging
59	63	Roll to 60+
77		ICP/RRD/Exp/Inv
78	0	Bact to Current
79	12	Roll Back to 1 - 30
80	31	Roll Back to 31 - 60
81	86	Same Aging
82	93	Roll to 90+
92		ICP/RRD/Exp/Inv
93	0	Bact to Current
94	15	Roll Back to 1 - 30
95	36	Roll Back to 31 - 60
96	67	Roll Back to 61 - 90
97	100	Same Aging
98	192	Roll to 180+
185		ICP/RRD/Exp/Inv
186	0	Bact to Current
187	17	Roll Back to 1 - 30
188	39	Roll Back to 31 - 60
189	89	Roll Back to 61 - 90
190	98	Roll Back to 91 - 180
191	200	Same Aging
192	215	Roll to WO

=IF(AND(A2>=0;B2="");"ICP/RRD/Exp/Inv";IF(AND(A2=0;B2=0);"Current";IF(AND(A2>0;B2>1*B2<=30);"Roll to 1 - 30", i mean like this but I am confused to continue with mutliple IF, with the full classification above.

 

[jasonb75]

Multiple IF's are the wrong way to go about it.

There is nothing in your reply to say that my suggestion does not / will not work as required.

Column A contains the lower limit for classification A, column B contains the upper limit. Columns C and D are the same for classification B.
For an equal criteria (blank or 0) both columns must contain the same value.

Columns F and G replicate columns A and B in your lower table with the formula (and results) in column I.
Column H can be removed, I just left it in there with your original results to compare.

 

[Joko Purnomo]

Multiple IF's are the wrong way to go about it.

There is nothing in your reply to say that my suggestion does not / will not work as required.

Column A contains the lower limit for classification A, column B contains the upper limit. Columns C and D are the same for classification B.
For an equal criteria (blank or 0) both columns must contain the same value.

Columns F and G replicate columns A and B in your lower table with the formula (and results) in column I.
Column H can be removed, I just left it in there with your original results to compare.

Thank you for the help, Is there another way that is simpler, without creating additional columns lower and upper limit. Just look at this example, an the result are like this. Helm me for the formula. Thank you very much. ???

column A	column B	Result Example
0		ICP/RRD/Exp/Inv
0	0	Current
0	12	Roll to 1 - 30
10		ICP/RRD/Exp/Inv
11	0	Bact to Current
12	13	Same Aging
13	35	Roll to 30+
55		ICP/RRD/Exp/Inv
56	0	Bact to Current
57	11	Roll Back to 1 - 30
58	57	Same Aging
59	63	Roll to 60+
77		ICP/RRD/Exp/Inv
78	0	Bact to Current
79	12	Roll Back to 1 - 30
80	31	Roll Back to 31 - 60
81	86	Same Aging
82	93	Roll to 90+
92		ICP/RRD/Exp/Inv
93	0	Bact to Current
94	15	Roll Back to 1 - 30
95	36	Roll Back to 31 - 60
96	67	Roll Back to 61 - 90
97	100	Same Aging
98	192	Roll to 180+
185		ICP/RRD/Exp/Inv
186	0	Bact to Current
187	17	Roll Back to 1 - 30
188	39	Roll Back to 31 - 60
189	89	Roll Back to 61 - 90
190	98	Roll Back to 91 - 180
191	200	Same Aging
192	215	Roll to WO

 

[jasonb75]

Is there another way that is simpler, without creating additional columns lower and upper limit.

That is a contradiction in terms. Without the additional columns it will be much more complicated.

 

[Joko Purnomo]

That is a contradiction in terms. Without the additional columns it will be much more complicated.

Start A	End A	Start B	End B	Result C	Example A	Example B	Result Example	Formula result
0​	0​			ICP/RRD/Exp/Inv	0​		ICP/RRD/Exp/Inv	Roll to 1 - 30
0​	0​	0​	0​	Current	0​	0​	Current	Roll to 1 - 30
0​	0​	1​	30​	Roll to 1 - 30	0​	0​	Roll to 1 - 30	#N/A
1​	30​			ICP/RRD/Exp/Inv	10​		ICP/RRD/Exp/Inv	Roll to 30+
1​	30​	0​	0​	Bact to Current	11​	0​	Bact to Current	Roll to 30+
1​	30​	1​	30​	Same Aging	12​	13​	Same Aging	Roll to 30+
1​	30​	31​	60​	Roll to 30+	13​	35​	Roll to 30+	Roll to 30+
31​	60​			ICP/RRD/Exp/Inv	55​		ICP/RRD/Exp/Inv	Roll to 60+
31​	60​	0​	0​	Bact to Current	56​	0​	Bact to Current	Roll to 60+
31​	60​	1​	30​	Roll Back to 1 - 30	57​	11​	Roll Back to 1 - 30	Roll to 60+
31​	60​	31​	60​	Same Aging	58​	57​	Same Aging	Roll to 60+
31​	60​	61​	90​	Roll to 60+	59​	63​	Roll to 60+	Roll to 60+
61​	90​			ICP/RRD/Exp/Inv	77​		ICP/RRD/Exp/Inv	Roll to 90+
61​	90​	0​	0​	Bact to Current	78​	0​	Bact to Current	Roll to 90+
61​	90​	1​	30​	Roll Back to 1 - 30	79​	12​	Roll Back to 1 - 30	Roll to 90+
61​	90​	31​	60​	Roll Back to 31 - 60	80​	31​	Roll Back to 31 - 60	Roll to 90+
61​	90​	61​	90​	Same Aging	81​	86​	Same Aging	Roll to 90+
61​	90​	91​	180​	Roll to 90+	82​	93​	Roll to 90+	Roll to 90+
91​	180​			ICP/RRD/Exp/Inv	92​		ICP/RRD/Exp/Inv	Roll to 180+
91​	180​	0​	0​	Bact to Current	93​	0​	Bact to Current	Roll to 180+
91​	180​	1​	30​	Roll Back to 1 - 30	94​	15​	Roll Back to 1 - 30	Roll to 180+
91​	180​	31​	60​	Roll Back to 31 - 60	95​	36​	Roll Back to 31 - 60	Roll to 180+
91​	180​	61​	90​	Roll Back to 61 - 90	96​	67​	Roll Back to 61 - 90	Roll to 180+
91​	180​	91​	180​	Same Aging	97​	100​	Same Aging	Roll to 180+
91​	180​	181​	210​	Roll to 180+	98​	192​	Roll to 180+	Roll to 180+
181​	210​			ICP/RRD/Exp/Inv	185​		ICP/RRD/Exp/Inv	Roll to WO
181​	210​	0​	0​	Bact to Current	186​	0​	Bact to Current	Roll to WO
181​	210​	1​	30​	Roll Back to 1 - 30	187​	17​	Roll Back to 1 - 30	Roll to WO
181​	210​	31​	60​	Roll Back to 31 - 60	188​	39​	Roll Back to 31 - 60	Roll to WO
181​	210​	61​	90​	Roll Back to 61 - 90	189​	89​	Roll Back to 61 - 90	Roll to WO
181​	210​	91​	180​	Roll Back to 91 - 180	190​	98​	Roll Back to 91 - 180	Roll to WO
181​	210​	181​	210​	Same Aging	191​	200​	Same Aging	Roll to WO
181​	210​	210​	1.00E+100​	Roll to WO	192​	215​	Roll to WO	Roll to WO

i have try your solution, but it's not right.

May be my other friends can help me?

Thank you

 

[jasonb75]

i have try your solution, but it's not right.

It is right.

I have no idea how you have broken it but you can see in post 2 that the formula results are correct!