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!
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!