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

 

[awoohaw]

I don't understand what you're asking completely. But, does this help:

Book1
	B	C	D	E	F	G
1			30+	30 to 89	30 to 210	90 to 210
2	31	30+	TRUE	TRUE	TRUE	FALSE
3	50	30+	TRUE	TRUE	TRUE	FALSE
4	72	30+	TRUE	TRUE	TRUE	FALSE
5	98	30+ also 90+	TRUE	FALSE	TRUE	TRUE
6	105	30+ also 90+	TRUE	FALSE	TRUE	TRUE
7	32	30+	TRUE	TRUE	TRUE	FALSE
8	84	30+	TRUE	TRUE	TRUE	FALSE
Sheet2

Cell Formulas
Range	Formula
D2:D8	D2	=B2>=30
E2:E8	E2	=AND(B2>=30,B2<=90)
F2:F8	F2	=AND(B2>=30,B2<=210)
G2:G8	G2	=AND(B2>=90,B2<=210)

 

[Joko Purnomo]

I don't understand what you're asking completely. But, does this help:

Book1
	B	C	D	E	F	G
1			30+	30 to 89	30 to 210	90 to 210
2	31	30+	TRUE	TRUE	TRUE	FALSE
3	50	30+	TRUE	TRUE	TRUE	FALSE
4	72	30+	TRUE	TRUE	TRUE	FALSE
5	98	30+ also 90+	TRUE	FALSE	TRUE	TRUE
6	105	30+ also 90+	TRUE	FALSE	TRUE	TRUE
7	32	30+	TRUE	TRUE	TRUE	FALSE
8	84	30+	TRUE	TRUE	TRUE	FALSE
Sheet2

Cell Formulas
Range	Formula
D2:D8	D2	=B2>=30
E2:E8	E2	=AND(B2>=30,B2<=90)
F2:F8	F2	=AND(B2>=30,B2<=210)
G2:G8	G2	=AND(B2>=90,B2<=210)

Thanks for helping, That's right, but how can the results be made in one column, with 2 results, 30 up and 90 up.

Thank you very much

 

[Alex Blakenburg]

You were asked in a previous thread to update your profile to show what version of Excel you are using since the best solution is often dependant on the version.
My understanding from that thread is that you are using Excel 2016.
Please update your profile.

Also if you show us the column & row references for your data you will get a solution tailored to your data which will make it easier for you to try the suggestions.

Do you simply want something like this ?

Book1
	B	C
1
2	31	30+
3	50	30+
4	72	30+
5	98	90+
6	105	90+
7	32	30+
8	84	30+
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=IF(B2>=90,"90+",IF(B2>=30,"30+","Current"))

 

[awoohaw]

Thanks for helping, That's right, but how can the results be made in one column, with 2 results, 30 up and 90 up.

Thank you very much

I gave you two distinct ways to consider the 90+. Just use the formulas that fit your need inside nested if statements.

 

[Joko Purnomo]

You were asked in a previous thread to update your profile to show what version of Excel you are using since the best solution is often dependant on the version.
My understanding from that thread is that you are using Excel 2016.
Please update your profile.

Also if you show us the column & row references for your data you will get a solution tailored to your data which will make it easier for you to try the suggestions.

Do you simply want something like this ?

Book1
	B	C
1
2	31	30+
3	50	30+
4	72	30+
5	98	90+
6	105	90+
7	32	30+
8	84	30+
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=IF(B2>=90,"90+",IF(B2>=30,"30+","Current"))

Yes accurately i want like this, buat i mean : when I want only 30+, it means I have to short 30+ and 90+. Is there an existing 30+ is all 30 up formula, while when I need 90+ only 90 ups appear in 1 column?

Thank for helping me

 

[Joko Purnomo]

Yes accurately i want like this, buat i mean : when I want only 30+, it means I have to short 30+ and 90+. Is there an existing 30+ is all 30 up formula, while when I need 90+ only 90 ups appear in 1 column?

Thank for helping me

Version my excel 2016

 

[Alex Blakenburg]

Version my excel 2016

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.

 

[Alex Blakenburg]

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

 

[Alex Blakenburg]

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 & "+","")