If Statement

dbaphomet

New Member
Joined
Dec 9, 2009
Messages
10
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.
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Dec 9, 2009
Messages
10
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Dec 9, 2009
Messages
10

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
ABCDEFGHIJK
1
2Grapes:0:15
3Apples:0:19:00PassGrapes:0:15:00
4Apples:0:20:00
5Oranges:0:25:00
Sheet1
</vlookup(b2,$j$3:$k$5,2,0),"pass","")
 

dbaphomet

New Member
Joined
Dec 9, 2009
Messages
10

ADVERTISEMENT

I think your formula was cut off
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
=IF(C2<VLOOKUP(B2,$J$3:$K$5,2,0),"Pass","")
 

dbaphomet

New Member
Joined
Dec 9, 2009
Messages
10
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
That is a formula. You could put all your options within the formula, but a table is probably more manageable.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top