Excel/Too many layers in formula
I have a formula I'm using which has sound syntax, but I'm getting this error: "the specified formula cannot be entered because it uses more levels of nesting than allowed in the current file format."
Here is the formula:
=IF(C21="Muda","Aims to reduce or eliminate of any one of the 8 Lean wastes (i.e. defect, overproduction, waiting, non-utilized talent, unnecessary transportation, excess inventory, excessive motion, excess processing)",IF(C21="Dollars", "Hard dollars (not FTE) - if your project aims to either increase revenue for the company, or reduce cost (ex. Overtime; reduced office supplies)", IF(C21="SoftDollars","Dollar savings were realized but cannot be clearly accounted for.",IF(C21="FTE"," Number of FTE (not $) - Project is able to reduce FTE cost; or if FTE reduction is NOT actually realized (ex. FTE assigned other tasks within the same dept.)", IF(C21="CycleTime", "Aims to cut cycle times in a process(es), or the project seeks to reduce lead times of a product/service.",IF(C21="BSCValues", "Aims to impact any of the following values:
- Trusted and Trusting
- Valuing People
- Results Oriented
- Agile and Nimble
- Creative Solutioning
- Customer Centric",IF(C21="Mandate","Aims to address legislative or regulatory obligations",IF(C21="Adherence/Compliance", "Seeks to address internal (company) policy issues or align a process(es) with company policy.", IF(C21="Other", "Projects which:
1) Map the Value Stream
2) Map the Current State process
3) Establish Flow, or
4) Establish Pull",IF(C21="Quality Scores", "Project seeks to improve Audit scores in a particular process or output.
(Ex: Project that directly reduces the LG audit errors)", IF(C21="Customer Satisfaction", "Project seeks to impact how Customers perceive I&B's services/outputs.","-")))))))))))
It's basically a series of IF statements. Excel will except this if I trim off the last 3 statements.
Do you know of a way to shorten this statement to allow Excel 2013 to use it?
I'm open to VBA as well.
Excel has a limitation that it can allow only a maximum if 7 if statements within a loop.
If you want to use more than 7 conditions, then you'll either have to split the formula across 2 cells or use a macro.
A basic macro using "select case" could serve the purpose.