Can this be written as a formula?

  • Thread starter Thread starter Kennyatwork
  • Start date Start date
K

Kennyatwork

Hello everyone

This one is definitely too complicated for me but I've seen some wild
formulas on this forum......so here goes!

I want to compare two cells in the same row {H1 and K1} then produce a
result of either Pass, Fail or Void into another cell in the same row.

{explained as simply as I can - }

H1 contains either "High", "Medium" or "Low"

If H1 is "High" and K1 is less than 24:00 then "Pass"
If H1 is "Medium" and K1 is less than 48:00 then "Pass"
If H1 is "Low" and K1 is less than 72:00 then "Pass"
If H1 is " " {empty} then Void
If the above criteria are not met then "Fail"

Can it be done in one formula or can someone suggest another way?

TIA
Kenny

Office 97 and 2000
 
Hi

Try this:
=IF(AND(H1="High",K1<24),"Pass",IF(AND(H1="Medium",K1<48),"Pass",IF(AND(H1="
Low",K1<72),"Pass",IF(H1="","Void","Fail"))))

This needs to be entered on one line. I've taken your Void option as being
"" rather than " "

Andy.
 
Hi
try
=IF(OR(AND(H1="High",K1<24),AND(H1="Medium",K1<48),AND(H1="Low",K1<72))
,"Pass",IF(H1="","Void","Fail"))
 
I'm assuming 24:00 = 24 hrs. If so, here is another way:

=IF(K1="","Void",IF(ISNA(VLOOKUP(H1&INT(K1),
{"High0";"Medium0";"Medium1";"Low0";"Low1";"Low2"},1,0)),"F
ail","Pass"))

Watch the wrap.

HTH
Jason
Atlanta, GA
 
WOW! - that does the trick.

Thanks Guys


Jason Morin said:
I'm assuming 24:00 = 24 hrs. If so, here is another way:

=IF(K1="","Void",IF(ISNA(VLOOKUP(H1&INT(K1),
{"High0";"Medium0";"Medium1";"Low0";"Low1";"Low2"},1,0)),"F
ail","Pass"))

Watch the wrap.

HTH
Jason
Atlanta, GA
Click to expand...
 
Hello everyone

This one is definitely too complicated for me but I've seen some wild
formulas on this forum......so here goes!

I want to compare two cells in the same row {H1 and K1} then produce a
result of either Pass, Fail or Void into another cell in the same row.

{explained as simply as I can - }

H1 contains either "High", "Medium" or "Low"

If H1 is "High" and K1 is less than 24:00 then "Pass"
If H1 is "Medium" and K1 is less than 48:00 then "Pass"
If H1 is "Low" and K1 is less than 72:00 then "Pass"
If H1 is " " {empty} then Void
If the above criteria are not met then "Fail"

Can it be done in one formula or can someone suggest another way?

TIA
Kenny

Office 97 and 2000
Click to expand...

If the only possible entries in H1 are the three you denote, or a null string
(""), then:


=IF(H1="","Void",VLOOKUP(H1,{0,3,2,1;"High","Fail","Fail","Pass";"Medium","Fail","Pass","Fail";"Low","Pass","Fail","Fail"},1+MATCH(K1,{3,2,1},-1)))

is one way.


--ron
 
Back
Top