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
  • 1
    your question is unclear now. What the percentages mean? And how it's related to the picture? Mar 3, 2016 at 21:55

2 Answers 2

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

3
  • Sam, the sum will never exceed 1, so your formula always results "Fail" Mar 3, 2016 at 21:54
  • I mentioned above >0 or she might write 50% she can write any number conveinient
    – user555689
    Mar 3, 2016 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, 2016 at 7:52
1

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .