Can this be written as a formula?

[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

 

[Andy B]

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.

 

[Frank Kabel]

Hi
try
=IF(OR(AND(H1="High",K1<24),AND(H1="Medium",K1<48),AND(H1="Low",K1<72))
,"Pass",IF(H1="","Void","Fail"))

 

[Jason Morin]

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

 

[Kennyatwork]

WOW! - that does the trick.

Thanks Guys

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

 

[Ron Rosenfeld]

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

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