how do I get a result if one cell is X and the 2nd cell is Y where

C

catts22

A2 = hours [hh]:mm:ss (this is the result of another formula)
B2 = the number 1, 2, 3, or 4

What I need is:

If b2 = 1 and a2 is less than or equal to 4 (hours) return met target
If b2 = 1 and a2 is greater than 4, but less than 6 return warning
if b2 = 1 and a2 is greater than 6 return alert
if b2 = 2 and a2 is less than or equal to 9 return met target
if b2 = 2 and a2 is greater than 9 but less than 13.5 return warning
if b2 = 2 and a2 is greater than 13.5 return alert
if b2 = 3 and a2 is less than or equal to 27 return met target
if b2 = 3 and a2 is greater than 27 but less than 45 return warning
if b2 = 2 and a2 is greater than 45 return alert
if b2 = 4 return n/a

I have tried a bunch of different things (if/and/or/lookups) and nothing
works. Please help... trying to meet a deadline.

Thanks
 
T

T. Valko

You have some gaps in your time intervals:
If b2 = 1 and a2 is greater than 4, but less than 6 return warning
if b2 = 1 and a2 is greater than 6 return alert
Click to expand...

One of those has to be either said:
if b2 = 2 and a2 is greater than 9 but less than 13.5 return warning
if b2 = 2 and a2 is greater than 13.5 return alert
Click to expand...

One of those has to be either said:
if b2 = 3 and a2 is greater than 27 but less than 45 return warning
if b2 = 2 and a2 is greater than 45 return alert
Click to expand...

One of those has to be either <=45 or >=45

I would create a table then use a lookup formula.


--
Biff
Microsoft Excel MVP


catts22 said:
A2 = hours [hh]:mm:ss (this is the result of another formula)
B2 = the number 1, 2, 3, or 4

What I need is:

If b2 = 1 and a2 is less than or equal to 4 (hours) return met target
If b2 = 1 and a2 is greater than 4, but less than 6 return warning
if b2 = 1 and a2 is greater than 6 return alert
if b2 = 2 and a2 is less than or equal to 9 return met target
if b2 = 2 and a2 is greater than 9 but less than 13.5 return warning
if b2 = 2 and a2 is greater than 13.5 return alert
if b2 = 3 and a2 is less than or equal to 27 return met target
if b2 = 3 and a2 is greater than 27 but less than 45 return warning
if b2 = 2 and a2 is greater than 45 return alert
if b2 = 4 return n/a

I have tried a bunch of different things (if/and/or/lookups) and nothing
works. Please help... trying to meet a deadline.

Thanks
Click to expand...
 
S

Shane Devenshire

Hi,

You need to correct the logical problems in your stated question. For this
answer I have made some assumptions, which you can adjust:

=IF(OR(AND(A2<=4/24,B2=1),AND(A2<=9/24,B2=2),AND(A2<=27/24,B2=3)),"met
target",IF(OR(AND(A2<6/24,B2=1),AND(A2>9/24,A2<13.5/24,B2=2),AND(A2>27/24,A2<45/24,B2=3)),"warning",IF(B2=4,NA(),"alert")))

Note that your next to last condition reads

if b2 = 2 and a2 is greater than 45 return alert
but it looks like it should be
if b2 = 3 and a2 is greater than 45 return alert
 
C

catts22

Hi Shane, this worked great. Thanks so much :)

Could you take a look at this one and let me know where I've gone wrong?

Need:

If Time is less than or equal to 15 minutes = Meets Target
If Time is equal to or greater than 16 min but less than or equal to 45 mins
= Warning
If Time is greater than 45 min = Alert

Here is my try:

=IF(p2="","",IF(P2<=15,"Met
Target",IF(OR(P2>=16,P2<=45,"Warning"),IF(P2>45,"Alert"))))
Where p2 is the Time and is formatted as a mumber (eg 9.00)

Or

=IF(o2="","",IF(o2<=15,"Met
Target",IF(OR(o2>=16,o2<=45,"Warning"),IF(o2>45,"Alert"))))
Where 02 is the time and is the result of this formula and formatted to:
[mm]:ss (for example 09:31
=IF(N2="","",((NETWORKDAYS(G2,L2)-2)*9)/24+TIME(17,0,0)-I2+N2-TIME(8,0,0))

Note the o2 version is better if possible as it is more accurate


Thanks... by the way I've found a few of your other post to be very helpful
too. It is great to see the formula written out!

Shane Devenshire said:
Hi,

You need to correct the logical problems in your stated question. For this
answer I have made some assumptions, which you can adjust:

=IF(OR(AND(A2<=4/24,B2=1),AND(A2<=9/24,B2=2),AND(A2<=27/24,B2=3)),"met
target",IF(OR(AND(A2<6/24,B2=1),AND(A2>9/24,A2<13.5/24,B2=2),AND(A2>27/24,A2<45/24,B2=3)),"warning",IF(B2=4,NA(),"alert")))

Note that your next to last condition reads

if b2 = 2 and a2 is greater than 45 return alert
but it looks like it should be
if b2 = 3 and a2 is greater than 45 return alert

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


catts22 said:
A2 = hours [hh]:mm:ss (this is the result of another formula)
B2 = the number 1, 2, 3, or 4

What I need is:

If b2 = 1 and a2 is less than or equal to 4 (hours) return met target
If b2 = 1 and a2 is greater than 4, but less than 6 return warning
if b2 = 1 and a2 is greater than 6 return alert
if b2 = 2 and a2 is less than or equal to 9 return met target
if b2 = 2 and a2 is greater than 9 but less than 13.5 return warning
if b2 = 2 and a2 is greater than 13.5 return alert
if b2 = 3 and a2 is less than or equal to 27 return met target
if b2 = 3 and a2 is greater than 27 but less than 45 return warning
if b2 = 2 and a2 is greater than 45 return alert
if b2 = 4 return n/a

I have tried a bunch of different things (if/and/or/lookups) and nothing
works. Please help... trying to meet a deadline.

Thanks
Click to expand...
Click to expand...
 
D

David Biddulph

Firstly in your formula if your O2 is really an Excel time you need to
replace 15 by TIME(0,15,0), and similarly for 16 and 45. If P2 is a number
of minutes (from time*24*60), you are OK as they are.
Secondly you've included the text string "Warning" in your OR function.
Look at where your parentheses are. In fact I don't think your number of
opening and closing parentheses even match. You need to think about what
each pair of parentheses is doing.
Thirdly didn't you want an AND, rather than an OR? The OR would always be
true, even without the text included.
Fourthly decide what you want for your undefined conditions, in this case
for times between 15 and 16 minutes. If you define your conditions
appropriately you can test in turn and not need the AND for subsequent
conditions. IF(P2<=TIME(0,15,0),"Met target",IF(P2<=45,"Warning","Alert")).
You've already tested for values less than or equal 15, so you wouldn't then
need to test for greater than 15 (if that's where your next range starts),
and you've tested for less than or equal 45 so you then don't need to test
for greater than 45.
That'll do for starters. ...
--
David Biddulph
Hi Shane, this worked great. Thanks so much :)

Could you take a look at this one and let me know where I've gone
wrong?

Need:

If Time is less than or equal to 15 minutes = Meets Target
If Time is equal to or greater than 16 min but less than or equal to
45 mins = Warning
If Time is greater than 45 min = Alert

Here is my try:

=IF(p2="","",IF(P2<=15,"Met
Target",IF(OR(P2>=16,P2<=45,"Warning"),IF(P2>45,"Alert"))))
Where p2 is the Time and is formatted as a mumber (eg 9.00)

Or

=IF(o2="","",IF(o2<=15,"Met
Target",IF(OR(o2>=16,o2<=45,"Warning"),IF(o2>45,"Alert"))))
Where 02 is the time and is the result of this formula and formatted
to: [mm]:ss (for example 09:31
=IF(N2="","",((NETWORKDAYS(G2,L2)-2)*9)/24+TIME(17,0,0)-I2+N2-TIME(8,0,0))

Note the o2 version is better if possible as it is more accurate


Thanks... by the way I've found a few of your other post to be very
helpful too. It is great to see the formula written out!

Shane Devenshire said:
Hi,

You need to correct the logical problems in your stated question.
For this answer I have made some assumptions, which you can adjust:

=IF(OR(AND(A2<=4/24,B2=1),AND(A2<=9/24,B2=2),AND(A2<=27/24,B2=3)),"met
target",IF(OR(AND(A2<6/24,B2=1),AND(A2>9/24,A2<13.5/24,B2=2),AND(A2>27/24,A2<45/24,B2=3)),"warning",IF(B2=4,NA(),"alert")))

Note that your next to last condition reads

if b2 = 2 and a2 is greater than 45 return alert
but it looks like it should be
if b2 = 3 and a2 is greater than 45 return alert

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


catts22 said:
A2 = hours [hh]:mm:ss (this is the result of another formula)
B2 = the number 1, 2, 3, or 4

What I need is:

If b2 = 1 and a2 is less than or equal to 4 (hours) return met
target If b2 = 1 and a2 is greater than 4, but less than 6 return
warning
if b2 = 1 and a2 is greater than 6 return alert
if b2 = 2 and a2 is less than or equal to 9 return met target
if b2 = 2 and a2 is greater than 9 but less than 13.5 return warning
if b2 = 2 and a2 is greater than 13.5 return alert
if b2 = 3 and a2 is less than or equal to 27 return met target
if b2 = 3 and a2 is greater than 27 but less than 45 return warning
if b2 = 2 and a2 is greater than 45 return alert
if b2 = 4 return n/a

I have tried a bunch of different things (if/and/or/lookups) and
nothing works. Please help... trying to meet a deadline.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
C

catts22

Hi David,

Thanks! the IF(P2<=TIME(0,15,0),"Met target",IF(P2... worked
I think I was over thinking the formula.

David Biddulph said:
Firstly in your formula if your O2 is really an Excel time you need to
replace 15 by TIME(0,15,0), and similarly for 16 and 45. If P2 is a number
of minutes (from time*24*60), you are OK as they are.
Secondly you've included the text string "Warning" in your OR function.
Look at where your parentheses are. In fact I don't think your number of
opening and closing parentheses even match. You need to think about what
each pair of parentheses is doing.
Thirdly didn't you want an AND, rather than an OR? The OR would always be
true, even without the text included.
Fourthly decide what you want for your undefined conditions, in this case
for times between 15 and 16 minutes. If you define your conditions
appropriately you can test in turn and not need the AND for subsequent
conditions. IF(P2<=TIME(0,15,0),"Met target",IF(P2<=45,"Warning","Alert")).
You've already tested for values less than or equal 15, so you wouldn't then
need to test for greater than 15 (if that's where your next range starts),
and you've tested for less than or equal 45 so you then don't need to test
for greater than 45.
That'll do for starters. ...
--
David Biddulph
Hi Shane, this worked great. Thanks so much :)

Could you take a look at this one and let me know where I've gone
wrong?

Need:

If Time is less than or equal to 15 minutes = Meets Target
If Time is equal to or greater than 16 min but less than or equal to
45 mins = Warning
If Time is greater than 45 min = Alert

Here is my try:

=IF(p2="","",IF(P2<=15,"Met
Target",IF(OR(P2>=16,P2<=45,"Warning"),IF(P2>45,"Alert"))))
Where p2 is the Time and is formatted as a mumber (eg 9.00)

Or

=IF(o2="","",IF(o2<=15,"Met
Target",IF(OR(o2>=16,o2<=45,"Warning"),IF(o2>45,"Alert"))))
Where 02 is the time and is the result of this formula and formatted
to: [mm]:ss (for example 09:31
=IF(N2="","",((NETWORKDAYS(G2,L2)-2)*9)/24+TIME(17,0,0)-I2+N2-TIME(8,0,0))

Note the o2 version is better if possible as it is more accurate


Thanks... by the way I've found a few of your other post to be very
helpful too. It is great to see the formula written out!

Shane Devenshire said:
Hi,

You need to correct the logical problems in your stated question.
For this answer I have made some assumptions, which you can adjust:

=IF(OR(AND(A2<=4/24,B2=1),AND(A2<=9/24,B2=2),AND(A2<=27/24,B2=3)),"met
target",IF(OR(AND(A2<6/24,B2=1),AND(A2>9/24,A2<13.5/24,B2=2),AND(A2>27/24,A2<45/24,B2=3)),"warning",IF(B2=4,NA(),"alert")))

Note that your next to last condition reads

if b2 = 2 and a2 is greater than 45 return alert
but it looks like it should be
if b2 = 3 and a2 is greater than 45 return alert

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


:

A2 = hours [hh]:mm:ss (this is the result of another formula)
B2 = the number 1, 2, 3, or 4

What I need is:

If b2 = 1 and a2 is less than or equal to 4 (hours) return met
target If b2 = 1 and a2 is greater than 4, but less than 6 return
warning
if b2 = 1 and a2 is greater than 6 return alert
if b2 = 2 and a2 is less than or equal to 9 return met target
if b2 = 2 and a2 is greater than 9 but less than 13.5 return warning
if b2 = 2 and a2 is greater than 13.5 return alert
if b2 = 3 and a2 is less than or equal to 27 return met target
if b2 = 3 and a2 is greater than 27 but less than 45 return warning
if b2 = 2 and a2 is greater than 45 return alert
if b2 = 4 return n/a

I have tried a bunch of different things (if/and/or/lookups) and
nothing works. Please help... trying to meet a deadline.

Thanks
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count the remaining weekdays in a data range excluding holidays and start/end dates 5
Help!!! - logical function for someone not very logical 3
Generate Payment Due Date to exclude Weekends and Public Holiday 4
SMALL function to find X smallest number greater than Y 6
Conditional Formatting Driving Me Crazy 2
I need to evalute two cells and return a result 1
Conditional Formatting Confusion... 2
Excel Logical function 5

Top