Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF: Using CPEARSON Approach
I have more than 7 conditions to check. In a different discussion there was
a reference to a cpearson site that noted breaking down the conditions into manageable functions and then have a 'master' function. I've tried that but it only half works (I must be missing something). What I am trying to do is to determine a value to place into a cell (the data is concatenated with CHAR(10) for force new lines) based on the value selected from a validation list. There are two different IF conditions with corresponding 'defined names'. First condition set called Services::=IF(ApplicationQuestionnaire!$C$4="XGN", CELL("contents",G31),IF(ApplicationQuestionnaire!$ C$4="XIS",CELL("contents",G60),IF(ApplicationQuest ionnaire!$C$4="XMA",CELL("contents",G62),IF(Applic ationQuestionnaire!$C$4="XNX",CELL("contents",G64) )))) Second condition set called CoreApps::=IF(ApplicationQuestionnaire!$C$4="XAM", CELL("contents",G3),IF(ApplicationQuestionnaire!$C $4="XCL",CELL("contents",G19),IF(ApplicationQuesti onnaire!$C$4="XIM",CELL("contents",G39),IF(Applica tionQuestionnaire!$C$4="XRM",CELL("contents",G66), IF(ApplicationQuestionnaire!$C$4="XST",CELL("conte nts",G69),"NoFilesReferenced"))))) Each condition has a corresponding 'results' using the CELL("contents",G75) to identify the contents of each condition (wasn't sure if this was really necessary). The name of each of the results are ServicesResults and CoreAppResults, respectively. The 'master' formula to bring the conditions together is::=IF(ServicesResults,ServicesResults,CoreAppRes ults). When a value is selected for CoreApps the results of the condition display appropriately. When I select a value from Services I get #VALUE!<<. I would appreciate the help ... I'm lost otherwise! Thx ... Jeff |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF: Using CPEARSON Approach
This hsould get you started. For the first formula
=INDEX(G31:G64,LOOKUP(C4,{"XGN","XIS","XMA","XNX"} ,{1,30,32,34})) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "JeffRI" wrote in message ... I have more than 7 conditions to check. In a different discussion there was a reference to a cpearson site that noted breaking down the conditions into manageable functions and then have a 'master' function. I've tried that but it only half works (I must be missing something). What I am trying to do is to determine a value to place into a cell (the data is concatenated with CHAR(10) for force new lines) based on the value selected from a validation list. There are two different IF conditions with corresponding 'defined names'. First condition set called Services::=IF(ApplicationQuestionnaire!$C$4="XGN", CELL("contents",G31),IF(ApplicationQuestionnaire!$ C$4="XIS",CELL("contents",G60),IF(ApplicationQuest ionnaire!$C$4="XMA",CELL("contents",G62),IF(Applic ationQuestionnaire!$C$4="XNX",CELL("contents",G64) )))) Second condition set called CoreApps::=IF(ApplicationQuestionnaire!$C$4="XAM", CELL("contents",G3),IF(ApplicationQuestionnaire!$C $4="XCL",CELL("contents",G19),IF(ApplicationQuesti onnaire!$C$4="XIM",CELL("contents",G39),IF(Applica tionQuestionnaire!$C$4="XRM",CELL("contents",G66), IF(ApplicationQuestionnaire!$C$4="XST",CELL("conte nts",G69),"NoFilesReferenced"))))) Each condition has a corresponding 'results' using the CELL("contents",G75) to identify the contents of each condition (wasn't sure if this was really necessary). The name of each of the results are ServicesResults and CoreAppResults, respectively. The 'master' formula to bring the conditions together is::=IF(ServicesResults,ServicesResults,CoreAppRes ults). When a value is selected for CoreApps the results of the condition display appropriately. When I select a value from Services I get #VALUE!<<. I would appreciate the help ... I'm lost otherwise! Thx ... Jeff |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF: Using CPEARSON Approach
LOOKUP and INDEX are functions I have yet to play with but I'm getting there.
Where do you get the {1,30,32,34} from? Also, rather than doing IF statements, can I not just put all of the 'conditions' together as a single statement? That may simplify this even further. Thx for the help, Mr. Phillips! "Bob Phillips" wrote: This hsould get you started. For the first formula =INDEX(G31:G64,LOOKUP(C4,{"XGN","XIS","XMA","XNX"} ,{1,30,32,34})) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "JeffRI" wrote in message ... I have more than 7 conditions to check. In a different discussion there was a reference to a cpearson site that noted breaking down the conditions into manageable functions and then have a 'master' function. I've tried that but it only half works (I must be missing something). What I am trying to do is to determine a value to place into a cell (the data is concatenated with CHAR(10) for force new lines) based on the value selected from a validation list. There are two different IF conditions with corresponding 'defined names'. First condition set called Services::=IF(ApplicationQuestionnaire!$C$4="XGN", CELL("contents",G31),IF(ApplicationQuestionnaire!$ C$4="XIS",CELL("contents",G60),IF(ApplicationQuest ionnaire!$C$4="XMA",CELL("contents",G62),IF(Applic ationQuestionnaire!$C$4="XNX",CELL("contents",G64) )))) Second condition set called CoreApps::=IF(ApplicationQuestionnaire!$C$4="XAM", CELL("contents",G3),IF(ApplicationQuestionnaire!$C $4="XCL",CELL("contents",G19),IF(ApplicationQuesti onnaire!$C$4="XIM",CELL("contents",G39),IF(Applica tionQuestionnaire!$C$4="XRM",CELL("contents",G66), IF(ApplicationQuestionnaire!$C$4="XST",CELL("conte nts",G69),"NoFilesReferenced"))))) Each condition has a corresponding 'results' using the CELL("contents",G75) to identify the contents of each condition (wasn't sure if this was really necessary). The name of each of the results are ServicesResults and CoreAppResults, respectively. The 'master' formula to bring the conditions together is::=IF(ServicesResults,ServicesResults,CoreAppRes ults). When a value is selected for CoreApps the results of the condition display appropriately. When I select a value from Services I get #VALUE!<<. I would appreciate the help ... I'm lost otherwise! Thx ... Jeff |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF: Using CPEARSON Approach
Hey Bob ... I followed your example and continued it across all of the values
needed. However, what should be done if the value entered is not listed? I would like to default some text in there if I don't get a match. Any ideas? Thx, Bob. Jeff "Bob Phillips" wrote: This hsould get you started. For the first formula =INDEX(G31:G64,LOOKUP(C4,{"XGN","XIS","XMA","XNX"} ,{1,30,32,34})) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "JeffRI" wrote in message ... I have more than 7 conditions to check. In a different discussion there was a reference to a cpearson site that noted breaking down the conditions into manageable functions and then have a 'master' function. I've tried that but it only half works (I must be missing something). What I am trying to do is to determine a value to place into a cell (the data is concatenated with CHAR(10) for force new lines) based on the value selected from a validation list. There are two different IF conditions with corresponding 'defined names'. First condition set called Services::=IF(ApplicationQuestionnaire!$C$4="XGN", CELL("contents",G31),IF(ApplicationQuestionnaire!$ C$4="XIS",CELL("contents",G60),IF(ApplicationQuest ionnaire!$C$4="XMA",CELL("contents",G62),IF(Applic ationQuestionnaire!$C$4="XNX",CELL("contents",G64) )))) Second condition set called CoreApps::=IF(ApplicationQuestionnaire!$C$4="XAM", CELL("contents",G3),IF(ApplicationQuestionnaire!$C $4="XCL",CELL("contents",G19),IF(ApplicationQuesti onnaire!$C$4="XIM",CELL("contents",G39),IF(Applica tionQuestionnaire!$C$4="XRM",CELL("contents",G66), IF(ApplicationQuestionnaire!$C$4="XST",CELL("conte nts",G69),"NoFilesReferenced"))))) Each condition has a corresponding 'results' using the CELL("contents",G75) to identify the contents of each condition (wasn't sure if this was really necessary). The name of each of the results are ServicesResults and CoreAppResults, respectively. The 'master' formula to bring the conditions together is::=IF(ServicesResults,ServicesResults,CoreAppRes ults). When a value is selected for CoreApps the results of the condition display appropriately. When I select a value from Services I get #VALUE!<<. I would appreciate the help ... I'm lost otherwise! Thx ... Jeff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Bug or wrong approach | Excel Worksheet Functions | |||
Approach to Excel | Excel Discussion (Misc queries) | |||
Sum Time - strange result (site & cpearson researched) | Excel Worksheet Functions | |||
What is the right approach? | Excel Worksheet Functions | |||
How to approach this? | Excel Discussion (Misc queries) |