If Functions Intersection

[Joko Purnomo]

How to use if functions that intersection with each other , for example when due days 30 up when due days = > 30 and <= 210" while another part in the same column when due days 90 up means = > 90 and <= 210". 30+ is included all of 90+, meanwhile 90+ is 90 up.

Example:

Due Days : Result :
31 30+
50 30+
72 30+
98 30+ also 90+
105. 30+ also 90+
32. 30+
84 30+

How the formula, when we filter 30 up, all 30 up selected in the colom, and when we filter 90up, only dusle days with 90 day up will selected

Please help me sir! Thanks very much

 

[Joko Purnomo]

Please
• click on your name in the banner
• select the tab Your Account
• click on Account Details
• Scroll down and tick the box with Office Version 2016
• Then hit save.
Everyone helping will then be able to see your version and you won't get asked each time.

Thank for info bro

 

[Joko Purnomo]

I n

Not sure how much value this adds but you could also have cell that drives what it shows:

20240101 Simple If statement.xlsx
	B	C	D	E	F	G
1	Days Outstanding	Aging			Enter Over Due days (>= )	90
2	31
3	50
4	72
5	98	90+
6	105	90+
7	32
8	84
Sheet1 (3)

Cell Formulas
Range	Formula
C2:C8	C2	=IF($B2>=$G$1,$G$1 & "+","")

I need all the result in colomn C, 30+ and 90+, All 30+ are part of 90+, while 90+ are 90 up only. I want these 2 conditions to be displayed in one column. Is it possible? Can I just sort one of them? Can help me

Thank

 

[Joko Purnomo]

If you are just trying to filter the rows is there any reason you don't just use the filter option ?

View attachment 104168

It can alternative manually, but i need in formula for speedup my job before vipot action

 

[Joko Purnomo]

Please
• click on your name in the banner
• select the tab Your Account
• click on Account Details
• Scroll down and tick the box with Office Version 2016
• Then hit save.
Everyone helping will then be able to see your version and you won't get asked each time.

Done thanks

 

[Alex Blakenburg]

Show us what you want in column C, make sure you show all scenarios ie 5 days, 30 days etc
If you are using picture please include the row and column references.

PS: thanks for updating your profile

 

[Joko Purnomo]

Show us what you want in column C, make sure you show all scenarios ie 5 days, 30 days etc
If you are using picture please include the row and column references.

PS: thanks for updating your profile

All scenario
0 = "Current"
1-30 = "1-30"
31-210 = "30+"
91-210 = "90+"

All part 30+ is 90+, meanwhile 90+ only 90 up

Thanks

 

[Alex Blakenburg]

Please mock it up in a spreadsheet and show us what it should look like in a picture of the sheet.
We can't have a single cell show 30+ as well as 90+ (for 91-210 which you also have covered with 31-210), and I am not sure that concatenating the 2 of text makes sense. ie "30+ & 90+"

 

[Joko Purnomo]

Please mock it up in a spreadsheet and show us what it should look like in a picture of the sheet.
We can't have a single cell show 30+ as well as 90+ (for 91-210 which you also have covered with 31-210), and I am not sure that concatenating the 2 of text makes sense. ie "30+ & 90+"

If the scenario isn't make sense with single cell, how about if the scenario like this :

All scenario

Column B
0 = "Current"
1-30 = "1-30"
31-90 = "31-90"
91-210 = "90+"

column C
Show the "30+ and 90+" 30+ is "1-30" + 31-90" and then 90+ is 90+ from column B, meanwhile other scenario False

Help me, with treat 2 kolom

 

[Alex Blakenburg]

This uses a lookup table. You can move that to another sheet. Ideally you would convert it to a table but you don't need to.

20240101 Simple If statement Joko Purnomo.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		Days Outstanding	Aging							From	To	Aging
2		31	31-90							-999	0	Current
3		50	31-90							1	30	1-30
4		72	31-90							31	90	31-90
5		98	91-210							91	210	91-210
6		105	91-210							211		Over 210
7		32	31-90
8		84	31-90
9		0	Current
10		300	Over 210
11		-10	Current
Lookup Version

Cell Formulas
Range	Formula
C2:C11	C2	=VLOOKUP($B2,$J$2:$L$6,3,TRUE)

 

[Joko Purnomo]

This uses a lookup table. You can move that to another sheet. Ideally you would convert it to a table but you don't need to.

20240101 Simple If statement Joko Purnomo.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		Days Outstanding	Aging							From	To	Aging
2		31	31-90							-999	0	Current
3		50	31-90							1	30	1-30
4		72	31-90							31	90	31-90
5		98	91-210							91	210	91-210
6		105	91-210							211		Over 210
7		32	31-90
8		84	31-90
9		0	Current
10		300	Over 210
11		-10	Current
Lookup Version

Cell Formulas
Range	Formula
C2:C11	C2	=VLOOKUP($B2,$J$2:$L$6,3,TRUE)

How about use "IF" function can you help me, thanks very much