You are here:

Excel/Too many layers in formula

Advertisement


Question
Hi Gulshan,

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
- Interconnected
- 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.

Thanks!

Answer
Dear Jeff,
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.

Thanks,
Gulshan
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


GULSHAN PURSWANI

Expertise

I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.

Experience

I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Education/Credentials
Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2016 About.com. All rights reserved.