1

I have this table where I need to get Pass or Fail result.

  • For column A if yes or n/a: 50% pass / no: 0% pass
  • For column B if yes: 15% pass / no: 0% pass
  • For column C if yes: 35% pass / no: 0% pass

Please help me to find a formula to get Pass or Fail accordingly to the above percentages.

Table

  • 1
    your question is unclear now. What the percentages mean? And how it's related to the picture? – Máté Juhász Mar 3 '16 at 21:55
0

You can use this Formula in D2 to sum the result >0 or >=50% or your choice gives you pass =IF(IF(OR(A2="yes",A2="n/a")=TRUE,50%,0)+IF(B2="yes",15%,0)+IF(C2="yes",35%,0)=>50%,"Pass","Fail") and drag it down

  • Sam, the sum will never exceed 1, so your formula always results "Fail" – Máté Juhász Mar 3 '16 at 21:54
  • I mentioned above >0 or she might write 50% she can write any number conveinient – user555689 Mar 3 '16 at 22:06
  • Thank you guys for your help. I have solved the issue now and got the right function. You are the best! – Adriana Mar 4 '16 at 7:52
2

Your question is unclear. If this doesn't solve your problem, it should at least give you the tools to do so. If you clarify, I can edit my answer based on your new information. I'm going to assume that <=50% is fail, >50% is pass, and your three conditions are summed.

So, breaking down your three conditions, you get:

  • =IF(A2="no",0,0.5)
  • =IF(B2="yes",0.15,0)
  • =IF(C2="yes",0.35,0)

Because each of these formulas simply resolve to a number, you can add them together with SUM()

SUM(IF(A2="no",0,0.5),IF(B2="yes",0.15,0),IF(C2="yes",0.35,0)) will give just another number (0.35 in this case) which can be compared to your definition of passing (greater than 50%) and used in the logical test for one last IF().

The result is:

=IF(SUM(IF(A2="no",0,0.5),IF(B2="yes",0.15,0),IF(C2="yes",0.35,0))>0.5,"pass","fail")

In this case, I chose to use SUM() purely for readability purposes. You could have also just done
=IF(IF(A2="no",0,0.5)+IF(B2="yes",0.15,0)+IF(C2="yes",0.35,0)>0.5,"pass","fail")
but I think that's marginally harder to understand by inspection.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.