Forum Discussion
Need Help with Nesting multiple If and statements
Hello Everyone,
i am working on a spreadsheet that will allow me to prompt data based on different scenarios from drop down menus. The problem i am running into is that i have capped out the allowed amount of nesting in excel, but each scenario is supposed to yield a specific result. Wanted to know if i could get help with this or possibly determine another solution that would help me? I attached a snapshot that shows some of the logic that i developed based on each scenario but have no luck in capturing it all in one cell as i know it is a lot. I appreciate any guidance in advance, Thank you!
2 Replies
- I've never seen such a Formula in my life,,, it's as longer as a train,,,, and I'm surprised if you are not getting an error, violation of maximum allowed characters in formula,,, better share the WB with us, along with the Criteria you are trying with If !!
I would try to set up a table that contains the values for each scenario and then use a lookup function to match the specific scenario to the table entries.
For some of the conditions that are testing number values, you may need to set up a MIN and MAX column if the condition has several different thresholds. Just be mindful that you should not set overlapping MIN/MAX ranges (where you are testing for <=100 and >=100 instead of <=100 and >100).
I attached a small example. Since the formula is an array formula, you may need to hit Ctrl+Shift+Enter if you don't have office 365 and dynamic arrays.
