| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| large function result as reference for offset function | Excel Discussion (Misc queries) | |||
| copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
| LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
| Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
| Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Linear Mode
