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

Muktar888

New Member
Joined
Apr 30, 2017
Messages
16
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!
Excel Formula.png
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,335
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
AOP
1
2
3
41CLIII
51RTCQI
6CLIII
7RTCQI
8eLabs1eLabs
9Assessment of understanding: Assessed 1 of which 1 passed and 0 failed.
10Rejection Rate is Issue
11Intervention Data: Challenge encountered
12 1Assessment of understanding: Assessed
13 1
14 0
15
16Consumables out of stock
17
180Expired Lab Stocks
19
201Rejection Rate is Issue
21777
22
230There is an IQC issue
24
25Challenge encounteredIntervention Data
Sheet21
Cell Formulas
RangeFormula
A6:A14A6=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.
 
Last edited:

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
993
Office Version
  1. 2016
Platform
  1. Windows
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.

Cell Formulas
RangeFormula
Q2Q2=IF(O4=1,"CLIII")
Q3Q3=IF(O5=1,"RTCQI")
Q4Q4=IF(O8=1,"eLabs")
Q5Q5=IF(O12>0,"Assessment of understanding: Assessed "&O12&" of which "&O13&" passed and "&O12-O13&" failed.")
Q6Q6=IF(O16<>"","Consumables out of stock: "&O16)
Q7Q7=IF(O18="Yes","Expired Lab Stocks: "&O18)
Q8Q8=IF(O20=1,"Rejection Rate is "&O21)
Q9Q9=IF(O23=1,"There is an IQC Issue")
Q10Q10=IF(O25<>"","Intervention Data: "&O25)
A6:A14A6=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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,335
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,815
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

@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

New Member
Joined
Apr 30, 2017
Messages
16
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
AOP
1
2
3
41CLIII
51RTCQI
6CLIII
7RTCQI
8eLabs1eLabs
9Assessment of understanding: Assessed 1 of which 1 passed and 0 failed.
10Rejection Rate is Issue
11Intervention Data: Challenge encountered
12 1Assessment of understanding: Assessed
13 1
14 0
15
16Consumables out of stock
17
180Expired Lab Stocks
19
201Rejection Rate is Issue
21777
22
230There is an IQC issue
24
25Challenge encounteredIntervention Data
Sheet21
Cell Formulas
RangeFormula
A6:A14A6=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
AOP
1
2
3
41CLIII
51RTCQI
6CLIII
7RTCQI
8eLabs1eLabs
9Assessment of understanding: Assessed 1 of which 1 passed and 0 failed.
10Rejection Rate is Issue
11Intervention Data: Challenge encountered
12 1Assessment of understanding: Assessed
13 1
14 0
15
16Consumables out of stock
17
180Expired Lab Stocks
19
201Rejection Rate is Issue
21777
22
230There is an IQC issue
24
25Challenge encounteredIntervention Data
Sheet21
Cell Formulas
RangeFormula
A6:A14A6=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

New Member
Joined
Apr 30, 2017
Messages
16
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.

Cell Formulas
RangeFormula
Q2Q2=IF(O4=1,"CLIII")
Q3Q3=IF(O5=1,"RTCQI")
Q4Q4=IF(O8=1,"eLabs")
Q5Q5=IF(O12>0,"Assessment of understanding: Assessed "&O12&" of which "&O13&" passed and "&O12-O13&" failed.")
Q6Q6=IF(O16<>"","Consumables out of stock: "&O16)
Q7Q7=IF(O18="Yes","Expired Lab Stocks: "&O18)
Q8Q8=IF(O20=1,"Rejection Rate is "&O21)
Q9Q9=IF(O23=1,"There is an IQC Issue")
Q10Q10=IF(O25<>"","Intervention Data: "&O25)
A6:A14A6=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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,335
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! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,113,836
Messages
5,544,596
Members
410,624
Latest member
smartsanjiv64
Top