Nested If using And & OR in order to create a dynamic display

[Muktar888]

Hi All,

I cant seem to get this formula right after struggling for hours.

What I am trying to do is create a news feed which displays values in cells based on if the metrics.

If all metrics' criteria are met, then there will be 9 rows of info, if only 5 metrics are met, then only 5 rows of info will be displayed. The catch is it always needs to be start from A6 down to A14, if 5 metrics are met then 4 rows will be blank..

So metric 1, 3 & 5 criteria can be met, then those needs to be displayed from A6 to A9; if metric 2,3,4,5 criteria are met, then it again starts from A6 down to A10. The remaining cells will be blank.

This is my formula for all 9 metrics in the first cell (A6) which works fine.

=IF(O4=1,"CLIII",IF(AND(O4=0,O5=1),"RTCQI",IF(AND(O4=0,O5=0,O8=1),"eLabs",IF(AND(O4=0,O5=0,O8=0,O12>0),"Assessment of understanding: Assessed "&O12&" of which "&O13&" passed and "&O12-O13&" failed.",IF(AND(O4=0,O5=0,O8=0,O12=0,O16<>""),"Consumables out of stock: "&O16,IF(AND(O4=0,O5=0,O8=0,O12=0,O16="",O18="Yes"),"Expired Lab Stocks: "&O18,IF(AND(O4=0,O5=0,O8=0,O12=0,O16="",O18<>"Yes",O20=1),"Rejection Rate is "&O21,IF(AND(O4=0,O5=0,O8=0,O12=0,O16="",O18<>"Yes",O20=0,O23=1),"There is an IQC Issue",IF(AND(O4=0,O5=0,O8=0,O12=0,O16="",O18<>"Yes",O20=0,O23=0,O25<>""),"Intervention Data: "&O25,"")))))))))

A7:

=IF(O4=1,"CLIII",IF(AND(O4=0,O5=1),"RTCQI",IF(AND(O4=0,O5=0,O8=1),"eLabs",IF(AND(O4=0,O5=0,O8=0,O12>0),"Assessment of understanding: Assessed "&O12&" of which "&O13&" passed and "&O12-O13&" failed.",IF(AND(O4=0,O5=0,O8=0,O12=0,O16<>""),"Consumables out of stock: "&O16,IF(AND(O4=0,O5=0,O8=0,O12=0,O16="",O18="Yes"),"Expired Lab Stocks: "&O18,IF(AND(O4=0,O5=0,O8=0,O12=0,O16="",O18<>"Yes",O20=1),"Rejection Rate is "&O21,IF(AND(O4=0,O5=0,O8=0,O12=0,O16="",O18<>"Yes",O20=0,O23=1),"There is an IQC Issue",IF(AND(O4=0,O5=0,O8=0,O12=0,O16="",O18<>"Yes",O20=0,O23=0,O25<>""),"Intervention Data: "&O25,"")))))))))

A8:

=IF(O4=1,IF(AND(OR(O4=0,O4=1),O5=1),IF(AND(OR(O4=0,O4=1,O5=0,O5=1),O8=1),"eLabs",IF(AND(O4=0,O5=0,O8=0,O12>0),"Assessment of understanding: Assessed "&O12&" of which "&O13&" passed and "&O12-O13&" failed.",IF(AND(O4=0,O5=0,O8=0,O12=0,O16<>""),"Consumables out of stock: "&O16,IF(AND(O4=0,O5=0,O8=0,O12=0,O16="",O18="Yes"),"Expired Lab Stocks: "&O18,IF(AND(O4=0,O5=0,O8=0,O12=0,O16="",O18<>"Yes",O20=1),"Rejection Rate is "&O21,IF(AND(O4=0,O5=0,O8=0,O12=0,O16="",O18<>"Yes",O20=0,O23=1),"There is an IQC Issue",IF(AND(O4=0,O5=0,O8=0,O12=0,O16="",O18<>"Yes",O20=0,O23=0,O25<>""),"Intervention Data: "&O25,""))))))),""),"")

Just based on these 3 criteria s, when they are all met, the cells A6 to A8 populate correctly; when the first and third criteria is met, it also works properly, ie A6 & A7 are populated with the first and third criteria info while A8 is blank.

But when the first criteria is not met and the next two are met, A6 returns the correct data (Metric 2), but A7 returns "false" instead of returning Metric 3 criteria. I think it has to do something with placing my the arguments in the "IF" formula but cant think anymore.

Essentially the metrics are all binary (either 0 or 1, Yes or No & "" or <>"" so its just seems like a lot of quantity especially with all the concats.

Have attached a picture below just based on the 3 metrics... still need to get all 9 to display dynamically, as in if any metrics are not met, then the ones that are met, push up in display from A4.

The metric infomation changes when i change the name in A2, then the data in column O all changes, so I would like to populate cells A6:A14 accordingly.

If there is a better way of doing this altogether, i am all ears.

Thanks you in advance!

 

[Eric W]

Rather than having 9 very complicated, different formulas, which are very hard to maintain (as you've seen), let's try a different route:

Book1
	A														O	P
1
2
3
4															1	CLIII
5															1	RTCQI
6	CLIII
7	RTCQI
8	eLabs														1	eLabs
9	Assessment of understanding: Assessed 1 of which 1 passed and 0 failed.
10	Rejection Rate is Issue
11	Intervention Data: Challenge encountered
12															1	Assessment of understanding: Assessed
13															1
14															0
15
16																Consumables out of stock
17
18															0	Expired Lab Stocks
19
20															1	Rejection Rate is Issue
21															777
22
23															0	There is an IQC issue
24
25															Challenge encountered	Intervention Data
Sheet21

Cell Formulas
Range	Formula
A6:A14	A6	=IFERROR(CHOOSE(SMALL(CHOOSE({1,2,3,4,5,6,7,8,9},IF($O$4=1,1),IF($O$5=1,2),IF($O$8=1,3),IF($O$12>0,4),IF($O$16<>"",5),IF($O$18="Yes",6),IF($O$20=1,7),IF($O$23=1,8),IF($O$25<>"",9)),ROWS($A$6:$A6)),"CLIII","RTCQI","eLabs","Assessment of understanding: Assessed "&O$12&" of which "&O$13&" passed and "&O$12-O$13&" failed.","Consumables out of stock","Expired Lab Stocks","Rejection Rate is Issue","There is an IQC Issue","Intervention Data: "&$O$25),"")

Put the formula in A6, and copy down to A14. It works by evaluating all 9 of your conditions for your 9 cells, and if they pass, they generate a number from 1-9. Then the SMALL function takes the nth smallest number in that array (n determined by the number of rows down in column A the formula is), and sends it to the CHOOSE function which then returns the description you want. A bit trickier in concept than deeply nested IFs and ANDs, but I think overall easier to maintain, especially since there is only one formula, not 9. The P column is just what I added to keep track of what description went with what row, it's not needed.

Let me know how this works for you.

 

[Toadstool]

Hi Muktar888,

I see Eric W beat me with an alternative design and I agree that the nested IF is complex and would be difficult to maintain (by the way, your formulae didn't give me the same results as your image display and all those close parentheses are omitted ELSE conditions for the IF so one of those would be the FALSE you were seeing).

If you can truly have all 9 conditions then I'd build a table and populate it with the results or FALSE if the condition isn't met, then just display in A6 to A14 the table rows which aren't FALSE.

Muktar888.xlsx
	A														O	P	Q
1																	Criteria
2																	CLIII
3																	RTCQI
4															1		eLabs
5															1		Assessment of understanding: Assessed 1 of which 1 passed and 0 failed.
6	CLIII														1		Consumables out of stock: Tissues
7	RTCQI																Expired Lab Stocks: Yes
8	eLabs														1		Rejection Rate is 777
9	Assessment of understanding: Assessed 1 of which 1 passed and 0 failed.																There is an IQC Issue
10	Consumables out of stock: Tissues																Intervention Data: Challenge encountered
11	Expired Lab Stocks: Yes
12	Rejection Rate is 777														1
13	There is an IQC Issue														1
14	Intervention Data: Challenge encountered														0
15
16															Tissues
17
18															Yes
19
20															1
21															777
22
23															1
24
25															Challenge encountered
Muktar

Cell Formulas
Range	Formula
Q2	Q2	=IF(O4=1,"CLIII")
Q3	Q3	=IF(O5=1,"RTCQI")
Q4	Q4	=IF(O8=1,"eLabs")
Q5	Q5	=IF(O12>0,"Assessment of understanding: Assessed "&O12&" of which "&O13&" passed and "&O12-O13&" failed.")
Q6	Q6	=IF(O16<>"","Consumables out of stock: "&O16)
Q7	Q7	=IF(O18="Yes","Expired Lab Stocks: "&O18)
Q8	Q8	=IF(O20=1,"Rejection Rate is "&O21)
Q9	Q9	=IF(O23=1,"There is an IQC Issue")
Q10	Q10	=IF(O25<>"","Intervention Data: "&O25)
A6:A14	A6	=IFERROR(INDEX($Q$2:$Q$10,AGGREGATE(15,6,ROW($Q$2:$Q$10)-ROW($Q$1)/($Q$2:$Q$10<>FALSE),ROWS($A$6:A6))),"")

 

[Eric W]

Hmm. Sometimes I get so focused on finding a clever formula, that I miss the fact that sometimes the best answer is more than one formula. Toadstool's 9 helper formulas are much easier to understand individually, and the final AGGREGATE formula is standard enough that lots of people can help with it. If you have no objections to the helper cells (which you can hide somewhere out of view), go with Toadstool's version.

If you still want a single formula, I notice that I didn't get all the descriptions quite right. I'll fix them up if you want.

 

[Peter_SSs]

@Eric W
Hi Eric. Sorry if I have mentioned this before but just browsing this thread & noticed that you are using an older version of XL2BB. There has been a few more bug fixes that you might want to pick up on.

 

[Muktar888]

Rather than having 9 very complicated, different formulas, which are very hard to maintain (as you've seen), let's try a different route:

Book1
	A														O	P
1
2
3
4															1	CLIII
5															1	RTCQI
6	CLIII
7	RTCQI
8	eLabs														1	eLabs
9	Assessment of understanding: Assessed 1 of which 1 passed and 0 failed.
10	Rejection Rate is Issue
11	Intervention Data: Challenge encountered
12															1	Assessment of understanding: Assessed
13															1
14															0
15
16																Consumables out of stock
17
18															0	Expired Lab Stocks
19
20															1	Rejection Rate is Issue
21															777
22
23															0	There is an IQC issue
24
25															Challenge encountered	Intervention Data
Sheet21

Cell Formulas
Range	Formula
A6:A14	A6	=IFERROR(CHOOSE(SMALL(CHOOSE({1,2,3,4,5,6,7,8,9},IF($O$4=1,1),IF($O$5=1,2),IF($O$8=1,3),IF($O$12>0,4),IF($O$16<>"",5),IF($O$18="Yes",6),IF($O$20=1,7),IF($O$23=1,8),IF($O$25<>"",9)),ROWS($A$6:$A6)),"CLIII","RTCQI","eLabs","Assessment of understanding: Assessed "&O$12&" of which "&O$13&" passed and "&O$12-O$13&" failed.","Consumables out of stock","Expired Lab Stocks","Rejection Rate is Issue","There is an IQC Issue","Intervention Data: "&$O$25),"")

Put the formula in A6, and copy down to A14. It works by evaluating all 9 of your conditions for your 9 cells, and if they pass, they generate a number from 1-9. Then the SMALL function takes the nth smallest number in that array (n determined by the number of rows down in column A the formula is), and sends it to the CHOOSE function which then returns the description you want. A bit trickier in concept than deeply nested IFs and ANDs, but I think overall easier to maintain, especially since there is only one formula, not 9. The P column is just what I added to keep track of what description went with what row, it's not needed.

Let me know how this works for you.

Hey

Rather than having 9 very complicated, different formulas, which are very hard to maintain (as you've seen), let's try a different route:

Book1
	A														O	P
1
2
3
4															1	CLIII
5															1	RTCQI
6	CLIII
7	RTCQI
8	eLabs														1	eLabs
9	Assessment of understanding: Assessed 1 of which 1 passed and 0 failed.
10	Rejection Rate is Issue
11	Intervention Data: Challenge encountered
12															1	Assessment of understanding: Assessed
13															1
14															0
15
16																Consumables out of stock
17
18															0	Expired Lab Stocks
19
20															1	Rejection Rate is Issue
21															777
22
23															0	There is an IQC issue
24
25															Challenge encountered	Intervention Data
Sheet21

Cell Formulas
Range	Formula
A6:A14	A6	=IFERROR(CHOOSE(SMALL(CHOOSE({1,2,3,4,5,6,7,8,9},IF($O$4=1,1),IF($O$5=1,2),IF($O$8=1,3),IF($O$12>0,4),IF($O$16<>"",5),IF($O$18="Yes",6),IF($O$20=1,7),IF($O$23=1,8),IF($O$25<>"",9)),ROWS($A$6:$A6)),"CLIII","RTCQI","eLabs","Assessment of understanding: Assessed "&O$12&" of which "&O$13&" passed and "&O$12-O$13&" failed.","Consumables out of stock","Expired Lab Stocks","Rejection Rate is Issue","There is an IQC Issue","Intervention Data: "&$O$25),"")

Put the formula in A6, and copy down to A14. It works by evaluating all 9 of your conditions for your 9 cells, and if they pass, they generate a number from 1-9. Then the SMALL function takes the nth smallest number in that array (n determined by the number of rows down in column A the formula is), and sends it to the CHOOSE function which then returns the description you want. A bit trickier in concept than deeply nested IFs and ANDs, but I think overall easier to maintain, especially since there is only one formula, not 9. The P column is just what I added to keep track of what description went with what row, it's not needed.

Let me know how this works for you.

Hi Eric W,

That worked perfectly in terms of logic,exactly what i was looking for! I still cant understand why the IFs didnt work lol, but who cares, you shown me a neater and cleaner way of doing this in one formula as well. I went on to build using this formula using 2 previous submissions.

I amended the formula to incorp the minor concat detail that was left out but no biggie.

once again, that was what i was looking for. How did you come up with this?

 

[Muktar888]

Hi Muktar888,

I see Eric W beat me with an alternative design and I agree that the nested IF is complex and would be difficult to maintain (by the way, your formulae didn't give me the same results as your image display and all those close parentheses are omitted ELSE conditions for the IF so one of those would be the FALSE you were seeing).

If you can truly have all 9 conditions then I'd build a table and populate it with the results or FALSE if the condition isn't met, then just display in A6 to A14 the table rows which aren't FALSE.

Muktar888.xlsx
	A														O	P	Q
1																	Criteria
2																	CLIII
3																	RTCQI
4															1		eLabs
5															1		Assessment of understanding: Assessed 1 of which 1 passed and 0 failed.
6	CLIII														1		Consumables out of stock: Tissues
7	RTCQI																Expired Lab Stocks: Yes
8	eLabs														1		Rejection Rate is 777
9	Assessment of understanding: Assessed 1 of which 1 passed and 0 failed.																There is an IQC Issue
10	Consumables out of stock: Tissues																Intervention Data: Challenge encountered
11	Expired Lab Stocks: Yes
12	Rejection Rate is 777														1
13	There is an IQC Issue														1
14	Intervention Data: Challenge encountered														0
15
16															Tissues
17
18															Yes
19
20															1
21															777
22
23															1
24
25															Challenge encountered
Muktar

Cell Formulas
Range	Formula
Q2	Q2	=IF(O4=1,"CLIII")
Q3	Q3	=IF(O5=1,"RTCQI")
Q4	Q4	=IF(O8=1,"eLabs")
Q5	Q5	=IF(O12>0,"Assessment of understanding: Assessed "&O12&" of which "&O13&" passed and "&O12-O13&" failed.")
Q6	Q6	=IF(O16<>"","Consumables out of stock: "&O16)
Q7	Q7	=IF(O18="Yes","Expired Lab Stocks: "&O18)
Q8	Q8	=IF(O20=1,"Rejection Rate is "&O21)
Q9	Q9	=IF(O23=1,"There is an IQC Issue")
Q10	Q10	=IF(O25<>"","Intervention Data: "&O25)
A6:A14	A6	=IFERROR(INDEX($Q$2:$Q$10,AGGREGATE(15,6,ROW($Q$2:$Q$10)-ROW($Q$1)/($Q$2:$Q$10<>FALSE),ROWS($A$6:A6))),"")

THank You Toadstool!

I will see how this works during the course of the day! So blessed to have you guys to save the day!!!

 

[Eric W]

Hey


Hi Eric W,

That worked perfectly in terms of logic,exactly what i was looking for! I still cant understand why the IFs didnt work lol, but who cares, you shown me a neater and cleaner way of doing this in one formula as well. I went on to build using this formula using 2 previous submissions.

I amended the formula to incorp the minor concat detail that was left out but no biggie.

once again, that was what i was looking for. How did you come up with this?

Just played around and experimented. I knew what I wanted as the final goal, and kept trying things until I either achieved it, or had to give up. I had one version I was on the verge of posting, but I wasn't happy with how the IFs worked, and it had some complicated array manipulation. So I kept at it. It helps that I've learned some cool tricks here, like the CHOOSE({1,2,3,4,5,6,7,8,9} bit. That's not something you'd find in a basic formula, and without it the formula would be much tougher.

Anyway, glad we could help!