Looking for an alternate solution

  • Thread starter Thread starter covingj
  • Start date Start date
C

covingj

I'm looking to see if there is an alternate solution. I have end users who
will enter four values. Two calculations will be performed resulting in an
average and the high value. There are three tests that are performed to
detemine if the person meets or fails. I want to use the results and
determine if the person has met all three of the tests, and if so, to refer.
If the person fails any of the three criteria, then there will be no
referral. I have created the following formula, and it does work, but I'm
wondering if there may be a more effecient way to accomplish this, just in
case I have to expand the number of tests that have to be performed so maybe
I could use a range instead of checking each individual cell.

In the formula below, E2, E3, and E4 will equal either Meet or Fail

=IF(OR(E2="Fail",E3="Fail",E4="Fail"),"Do not refer","Refer")

No rush on this since I have a working solution, but I thank you all for
your hard work. You have assisted me on several projects that could not have
been completed without you.
 
Perhaps:
=IF(ISNUMBER(MATCH("Fail",E2:E4,0)),"Do not refer","Refer")

Checks to see if the word "Fail" appears in the array (which returns a
number). You can now simply expand your array as needed. You could even go
ahead and set it to E2:E100 right now, and as long as you don't have "Fail"
in any of the cells, it will still work.
 
I'm looking to see if there is an alternate solution. I have end users who
will enter four values. Two calculations will be performed resulting in an
average and the high value. There are three tests that are performed to
detemine if the person meets or fails. I want to use the results and
determine if the person has met all three of the tests, and if so, to refer.
If the person fails any of the three criteria, then there will be no
referral. I have created the following formula, and it does work, but I'm
wondering if there may be a more effecient way to accomplish this, just in
case I have to expand the number of tests that have to be performed so maybe
I could use a range instead of checking each individual cell.

In the formula below, E2, E3, and E4 will equal either Meet or Fail

=IF(OR(E2="Fail",E3="Fail",E4="Fail"),"Do not refer","Refer")

No rush on this since I have a working solution, but I thank you all for
your hard work. You have assisted me on several projects that could not have
been completed without you.
Click to expand...

=if(countif(e2:e4,"Fail"),"Do not Refer","Refer")
--ron
 
Thank you for your lightning fast response. MATCH did cross my mind, but
didn't put it together with ISNUMBER. Maybe when I grow up I will be as smart
as some of you, but being in my 40's, I'm running out of time.
 
Back
Top