If Statement

[dbaphomet]

First I want apologize for what maybe the obvious, but I'm struggling with a formula and I just can't find the resolution.

My question is "How can I reference text conditions in one column, time conditions in another, and put out the True/False statement using an if function?"

Example would be

If Column B2/B3/B4 contains:

Widgets:
Apples:
Oranges:

And Column C2/C3/C4 contains time values:

00:15:23
01:12:23
00:05:56

The Condition would be if B2="'Widgets", and C2>time(0,15,0),"Pass","", AND if B2='"Apples'", and C2<time(0,12,0),"fail" and so on.

I just can't get work right. Any help is greatly appreciated.

 

[Scott Huish]

Is it really only 3 items you are testing or is that just a sample?

Your post got cut off, but is the test actually a mix of greater than or less than comparisons or are they all the same, i.e. all either greater than or all less than?

 

[dbaphomet]

Yes, it did get cut off, but it is a greater than/less than time value if text condition applies for the corresponding column, and there are 3 to 4 different text options, and 3 to 4 time values. So Apples has to be done under 7 minutes, anything greater than 7 minutes it fails, and if the column B contains "grapes" and has a value of less than 8, pass. I just want it to be one formula for all 38,000 rows of data I have.

EXAMPLE
B C D
Fruit Produce Time Pass/Fail
1 Grapes 00:15:00 Fail
2 Apples 00:06:35 Pass
3 Oranges 00:12:25 Pass
4 Bananas 01:11:25 Fail

I hope this explanation is not too bad. Thanks for the help

 

[Scott Huish]

What I mean is not if it is the same value, but are all the PASS conditions based on being LESS than some time value?

 

[dbaphomet]

Yes , all conditions pass if being less that a specific time limit.

Grapes pass from 0 to 15 minutes
Apples pass from 0 to 20 minutes
Oranges and Bananas pass from 0 to 25 minutes

 

[Scott Huish]

Make a table like I did in J2:K5 somewhere in your spreadsheet, with the item and the high end of the time for that item, then use a formula like this:

=IF(C2<VLOOKUP(B2,$J$3:$K$5,2,0),"Pass","")<vlookup(b2,$j$3:$k$5,2,0),"pass","")

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1
2		Grapes:	0:15
3		Apples:	0:19:00	Pass						Grapes:	0:15:00
4										Apples:	0:20:00
5										Oranges:	0:25:00
Sheet1

</vlookup(b2,$j$3:$k$5,2,0),"pass","")

 

[dbaphomet]

I think your formula was cut off

 

[Scott Huish]

=IF(C2<VLOOKUP(B2,$J$3:$K$5,2,0),"Pass","")

 

[dbaphomet]

Thanks - I'll try the vlookup option. I was really looking for a formula that would cover this but this will do also.

 

[Scott Huish]

That is a formula. You could put all your options within the formula, but a table is probably more manageable.