Help on IF function

[jayjavina]

Hi all,

Here is my issue, I have this formula, and I think there is something wrong with it coz it’s not giving me the result I need.
What I want to accomplish is IF in the selected cells, all the values are the same (Pass) the overall result will be “PASS” or we can say “PASS” is overall DEFAULT score. Next is, IF by any chance one cell has a value of “Maybe”, then the overall score will change to “MAYBE”. Another scenario is when the value of one of the cells is “Fail” The overall score will automatically be “FAILED” though one cell is selected as “Maybe”. “ Fail” overrides the overall result. Last scenario is that ALL cells has a value of “Pass” BUT if cell I55 is “Exceed” then the overall score will be “EXCEED”.<o></o>
<o> </o>
In short, the rule is by default the overall score is “PASS” but if one of the cells is MAYBE then the overall score will be “MAYBE”.<o></o>
But if in any cell there is a value of “FAIL” the overall score will be “FAIL”<o></o>
“EXCEED” will only happen if the value in each cell is “PASS”.<o></o>
You cannot exceed if you have a value of “maybe” and “fail”.<o></o>
<o> </o>
This is my formula and am stuck! I don’t know where to put “exceed”<o></o>
<o> </o>
{=IF(OR(I6,I15,I21,I25,I30,I36,I49,I52="Fail"),"FAIL",IF(OR(I6,I15,I21,I25,I30,I36,I49,I52="Maybe"),"MAYBE","PASS"))}<o></o>
<o> </o>
Thanks for the help!!!<o></o>

 

[Domski]

Try something like:

=IF(I55="Exceed","Exceed",IF(OR(I6="Fail",I15="Fail",I21="Fail"),"Fail",IF(OR(I6="Maybe",I15="Maybe",I21="Maybe"),"Maybe","Pass")))

You'll forgive me for not typing out all the I cells in the OR functions but hopefully you get the idea.

Dom

 

[jayjavina]

Thanks for the help Domski but its the other way around. If i selected exceed in I52, the overall score is not moving already its stuck with exceed though there is a FAIL value in one of the cells.

FAIL overrides the overall score and there cant be an exceed if one of the cells has a value of MAYBE or FAIL.

Am trying to work around on the formula and still i cannot get my results its its quite long am thinking of something shorter if there is any.

Thanks!

 

[Domski]

I think you need to more clearly explain the logic.

From what you described I understood:

- If I55 is Exceed then it should result in Exceed
- If any cell (not including I55) is Fail it should result in Fail
- If any cell (not including I55) is Maybe it should result in Maybe
- Otherwise result will be Pass

Is this not correct?

Dom

 

[Domski]

As for shortening the formula it would be easier to have a table of linked cells with the results on another part of the sheet. You could then use COUNTIF rather than loads of OR options.

Dom

 

[crimson_b1ade]

maybe this:

=IF(AND(IF(ISERROR(MATCH("Fail",I6:I52,0)),IF(ISERROR(MATCH("Maybe",I6:I52,0)),"Pass","Maybe"),"Fail")="Pass",I55="Exceed"),"Exceed",IF(ISERROR(MATCH("Fail",I6:I52,0)),IF(ISERROR(MATCH("Maybe",I6:I52,0)),"Pass","Maybe"),"Fail"))

 

[jayjavina]

Hi Dom,

these things are ok but is it possible that even though i selected exceed and one of the values in the cell is maybe and fail, the overall result wont change to exceed since maybe and fail is in there

For example all the cells are passed, then i selected I55 then changed it to exceed the overall will be exceed. But if i change any of the cells (including I55) to maybe/fail the overall score should change to maybe/fail. Will it work?

- If all cells are pass, then that is the only time we can put exceed in i55

- If any cell (not including I55) is Fail it should result in Fail -OK
- If any cell (not including I55) is Maybe it should result in Maybe -OK
- Otherwise result will be Pass -OK

Really appreciate it!
Thanks

 

[Domski]

Try:

=IF(OR(I6="Fail",I15="Fail",I21="Fail"),"Fail",IF(OR(I6="Maybe",I15="Maybe",I21="Maybe"),"Maybe",IF(I55="Exceed","Exceed","Pass")))

Again expand the criteria in the OR to cover all the I cells that can hold Pass/Fail/Maybe.

Dom

 

[jayjavina]

Got it Dom! Many Thanks!!!

crimson_b1ade, am trying it as well thanks too bro!

Appreciate it!