|
Help with 'If' function
Hi there all, sorry I just cant get my head around nested ifs??
I have two columns I and K and the following criteria with desired return for the formula: I and K can be one of these values N/C, Start, Stop, Inc, Dec, BLANK, C/R Depending on the combination of I and K, I want to return either Yes or No. Here are the I and K = outcome options: N/C and N/C = Yes N/C and Start= Yes N/C and Stop = No N/C and Inc = Yes N/C and Dec = Yes N/C and BLANK= Yes N/C and C/R = Yes BLANK and BLANK = No Start and N/C= No Start and Stop = No Stop and N/C= Yes Inc and N/C=Yes Inc and Dec= Yes There are more! Can i combine all of the Yes options? Whats the best or easiest way to deal with this? Many thanks Theo. |
Help with 'If' function
I would suggest that you build up a 2-column table somewhere showing
all those combinations in one column and the desired outcome in the other column like this: N/CN/C Yes N/CStart Yes N/CStop No N/CInc Yes N/CDec Yes N/CBLANK Yes N/CC/R Yes BLANKBLANK No StartN/C No StartStop No StopN/C Yes IncN/C Yes IncDec Yes Suppose this is in X1:Y13. Then you can just use this formula on row 2: =VLOOKUP(I2&K2,X$1:Y$13,2,0) and copy down as required. Hope this helps. Pete On May 5, 12:48*am, TBA wrote: Hi there all, sorry I just cant get my head around nested ifs?? I have two columns I and K and the following criteria with desired return for the formula: I and K can be one of these values N/C, Start, Stop, Inc, Dec, BLANK, C/R Depending on the combination of I and K, I want to return either Yes or No. * Here are the I and K = outcome options: N/C and N/C = Yes N/C and Start= Yes N/C and Stop = No N/C and Inc = Yes N/C and Dec = Yes N/C and BLANK= Yes N/C and C/R = Yes BLANK and BLANK = No Start and N/C= No Start and Stop = No Stop and N/C= Yes Inc and N/C=Yes Inc and Dec= Yes There are more! Can i combine all of the Yes options? Whats the best or easiest way to deal with this? *Many thanks *Theo. |
Help with 'If' function
thanks Pete-0 alot more straight forward than I feared! works a treat, here's
to GMT- saves me every time... "Pete_UK" wrote: I would suggest that you build up a 2-column table somewhere showing all those combinations in one column and the desired outcome in the other column like this: N/CN/C Yes N/CStart Yes N/CStop No N/CInc Yes N/CDec Yes N/CBLANK Yes N/CC/R Yes BLANKBLANK No StartN/C No StartStop No StopN/C Yes IncN/C Yes IncDec Yes Suppose this is in X1:Y13. Then you can just use this formula on row 2: =VLOOKUP(I2&K2,X$1:Y$13,2,0) and copy down as required. Hope this helps. Pete On May 5, 12:48 am, TBA wrote: Hi there all, sorry I just cant get my head around nested ifs?? I have two columns I and K and the following criteria with desired return for the formula: I and K can be one of these values N/C, Start, Stop, Inc, Dec, BLANK, C/R Depending on the combination of I and K, I want to return either Yes or No. Here are the I and K = outcome options: N/C and N/C = Yes N/C and Start= Yes N/C and Stop = No N/C and Inc = Yes N/C and Dec = Yes N/C and BLANK= Yes N/C and C/R = Yes BLANK and BLANK = No Start and N/C= No Start and Stop = No Stop and N/C= Yes Inc and N/C=Yes Inc and Dec= Yes There are more! Can i combine all of the Yes options? Whats the best or easiest way to deal with this? Many thanks Theo. |
Help with 'If' function
You're welcome - thanks for feeding back.
Pete On May 5, 3:17*am, TBA wrote: thanks Pete-0 alot more straight forward than I feared! works a treat, here's to GMT- saves me every time... "Pete_UK" wrote: I would suggest that you build up a 2-column table somewhere showing all those combinations in one column and the desired outcome in the other column like this: N/CN/C * * * * * * *Yes N/CStart * * * * * *Yes N/CStop * * * * * * No N/CInc * * * * * * * *Yes N/CDec * * * * * * * Yes N/CBLANK * * * * Yes N/CC/R * * * * * * * Yes BLANKBLANK * *No StartN/C * * * * * * *No StartStop * * * * * * No StopN/C * * * * * * *Yes IncN/C * * * * * * * * Yes IncDec * * * * * * * * Yes Suppose this is in X1:Y13. Then you can just use this formula on row 2: =VLOOKUP(I2&K2,X$1:Y$13,2,0) and copy down as required. Hope this helps. Pete On May 5, 12:48 am, TBA wrote: Hi there all, sorry I just cant get my head around nested ifs?? I have two columns I and K and the following criteria with desired return for the formula: I and K can be one of these values N/C, Start, Stop, Inc, Dec, BLANK, C/R Depending on the combination of I and K, I want to return either Yes or No. * Here are the I and K = outcome options: N/C and N/C = Yes N/C and Start= Yes N/C and Stop = No N/C and Inc = Yes N/C and Dec = Yes N/C and BLANK= Yes N/C and C/R = Yes BLANK and BLANK = No Start and N/C= No Start and Stop = No Stop and N/C= Yes Inc and N/C=Yes Inc and Dec= Yes There are more! Can i combine all of the Yes options? Whats the best or easiest way to deal with this? *Many thanks *Theo.- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 02:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com