Multiple if functions that won't generate a too many argument error

Whilst Excel does indeed provide the capability to provide complex formulae by nesting functions within other functions it can be difficult, as you have found, to modify these. In organisations, these difficulties get magnified when the original developer of a spreadsheet moves on or leaves. It is little wonder that IT professionals often throw their hands up in horror when they discover that Excel is being used to manage some important aspect of a business.

One observation of your formula is that the nested IF's contain quite a lot of parameters: for example the time ranges "before today" (expressed as E13<TODAY()), "between 1 and 10 days", etc and the associated outcomes such as "Schedule CPD presentation...", "Query1", etc.

If these same parameters are used elsewhere in the spreadsheet and, for some reason, they need to change it can be difficult to identify all the formulas that require changing. Errors can easily occur by missing something that needs to be changed or by making inconsistent changes. Examples of changes are things like: "between 1 and 10 days" gets changed to "between 1 and 15 days" or split into "between 1 and 5 days" and "between 6 and 10 days". Even if a parameter is not scattered all over a workbook just adjusting a complex formula containing a lot of parameters can be less than straightforward (as you found).

Fortunately, embedding parameters within complex formula is a practice where there is often room for considerable improvement.

In your particular example, a simple parameter table showing the outcomes for different time ranges can be used. This makes the parameters immediately visible within the spreadsheet (rather than being hiding them in formulas). It allows the type of changes noted above to be made quickly and easily. Some of the "tricks" involved include

• using named ranges (helps reduce referencing errors when the number of parameters changes)
• use of INDEX/MATCH rather than the VLOOKUP function (Google it)
• use of the match_type argument in function MATCH (see the MATCH function documentation)

This approach is shown below. It is offered as an example of how a different approach can, perhaps, make things more transparent and easier to maintain into the future. Compare the parameter table (in range A1:C5) and the formula in cell E15 of the screenshot below with the formula you were trying to edit. Which is easier to understand? Or more pertinently, which is easier for someone else to understand?

The use of 999999 in cell A2 is another trick. It is the limit on how far into the future (in days) is valid for the first outcome ("Plan out..."). 999999 days is, of course, over 2500 years so it is a limit that is unlikely to be breached!