| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I get a result if one cell is X and the 2nd cell is Y where
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do I get a result if one cell is X and the 2nd cell is Y where
Setup a lookup table and use combination INDEX and MATCH functions
"catts22" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do I get a result if one cell is X and the 2nd cell is Y where
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 One of those has to be either <=6 or =6 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 One of those has to be either <=13.5 or =13.5 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 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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do I get a result if one cell is X and the 2nd cell is Y where
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(A29/24,A2<13.5/24,B2=2),AND(A227/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" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do I get a result if one cell is X and the 2nd cell is Y w
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(P245,"A lert")))) 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(o245,"A lert")))) 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" wrote: 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(A29/24,A2<13.5/24,B2=2),AND(A227/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" wrote: 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do I get a result if one cell is X and the 2nd cell is Y w
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 catts22 wrote: 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(P245,"A lert")))) 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(o245,"A lert")))) 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" wrote: 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(A29/24,A2<13.5/24,B2=2),AND(A227/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" wrote: 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do I get a result if one cell is X and the 2nd cell is Y w
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" wrote: 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 catts22 wrote: 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(P245,"A lert")))) 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(o245,"A lert")))) 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" wrote: 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(A29/24,A2<13.5/24,B2=2),AND(A227/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" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Concatenate text cell and formula cell result | Excel Worksheet Functions | |||
| Formula Bar F9 Result differs from cell result??? | Excel Worksheet Functions | |||
| Removing a formula from a cell once the result is in the cell | Excel Worksheet Functions | |||
| vlookup shows result one cell above the expected result | Excel Worksheet Functions | |||
| Advanced formula - Return result & Show Cell Reference of result | Excel Worksheet Functions | |||
Linear Mode
