IF Formula Involving Non-adjacent Cells Forming Array With Three Criteria (Pass, Fail, Blank Cells)

Zedrick13

Board Regular
Joined
Sep 8, 2018
Messages
100
This formula doesn't seem to work:

=IF(CHOOSE({1;2;3;4;5;6},Q33,S33,U33,W33,Y33,AA33)="","Unexecuted",IF(CHOOSE({1;2;3;4;5;6},Q33,S33,U33,W33,Y33,AA33)="Fail","Fail",IF(CHOOSE({1;2;3;4;5;6},Q33,S33,U33,W33,Y33,AA33)=OR("Pass",""))))

Does anyone have any idea how to fix it? The intention is:
1. If columns Q;S;U;W;Y;AA have at least 1 Fail, value for column O must be "Fail".
2. If columns Q;S;U;W;Y;AA are a mix of Pass and blanks only, it's a "Pass" for column O.
3. If columns Q;S;U;W;Y;AA are all blank, column O returns value "Unexecuted".

I used the CHOOSE function to create an array for the non-adjacent cells.
 
Last edited:
Sorry, @AlanY. I will make sure not to do it again (duplicating questions). The formula might have a little bit of a problem. I wonder if you could take a look at cell O292? The value at O292 is WIP but the cells I defined as array were all Pass.

https://drive.google.com/open?id=1h7KSZ2TIqP8SE0LlmMeKPM9hFrkhn1Hh

I also tried to put all Pass for all six cells defined in the array, but the value at cell O return WIP.
 
Last edited:
Upvote 0

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
because on that particular row you're only testing 2 cells, S922 & Y292 instead of 5; you need to change that part of formula to

Code:
=IF(SUMPRODUCT(--(ISNUMBER(SEARCH("Fail",CHOOSE({1;2},S292,Y292)))))>0,"Fail",IF(SUMPRODUCT(--(ISNUMBER(SEARCH("Pass",CHOOSE({1;2},S292,Y292)))))=[COLOR="#FF0000"]2[/COLOR],"Pass",IF(SUMPRODUCT(--(ISNUMBER(SEARCH("Pass",CHOOSE({1;2},S292,Y292)))))>0,"WIP","Unexecuted")))
 
Upvote 0
I see. That's brilliant. So, the formula should be different depending on the number of the values in the array. Anyway, I hope I am not imposing on you. So, a tried a couple of scenarios when there are 6 values in the array. And, I seem to be having issues with the formula at rows 57 to 61. Hope you can take another look.

https://drive.google.com/open?id=1h7KSZ2TIqP8SE0LlmMeKPM9hFrkhn1Hh
 
Last edited:
Upvote 0
Sorry about the question above. I tried checking the formula again. It turns out I just have to change the number after the array. I'll keep checking.
 
Last edited:
Upvote 0
actually that was my mistake, to check on 6 cells, that value should be 6 anyway.
don't know why I put 5 there in the first place
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top