Multiple IF Statements

[crbenn01]

on "Roadmap" tab. I'm running into too many IF statements, where in Excel I have no problem. Each week should populate information based on an action plan. I'm not the most advance when it comes to spreadsheets (ie Excel), but I can't think of any other way to make make this work.

[FJCC]

You have allowed human readability to take precedence over computational functionality and are making this task very, very difficult. I think you need to back up and lay out the information so that you can use functions like VLOOKUP, MATCH and INDEX and not huge combinations of IF functions that will be hard to maintain and error prone. I cannot give you very specific advice about how to do that because I cannot untangle what you are trying to do.

For example, let's look at the beginning of the formula in Roadmap.H8

Code: Select all

=IF(E5=$Competencies.F3;$'1'.C9;IF(E5=$Competencies.F4;$'1'.D9;IF(E5=$Competencies.F5;$'1'.E9 ...)

You are trying to match the value in E5 with a value in Competencies column F (and columns G, H etc). As the match position walks down column F, the returned value walks across row 9 of the sheet '1'. But '1'.C9 is blank, '1'.D9 is part of a merged range, '1'.E9 does contain text, '1'.F9 has been merged away ... I just can't tell how to lay out the data.

Somewhere you should list all of the competencies currently in Competencies.F3:J10 in a single column, put the values to be returned in the adjacent column, and use a VLOOKUP. You may also make a pretty version of the competencies laid out in a matrix but the calculation should come from the single-column list. I know that is a lot of work but it will be much easier to debug and maintain.

[crbenn01]

Hi FJCC. So the IF statement is first matching the action plan (e5) that was selected from the first page. If it matches an action plan, each week pulls information for that week it's on. I was told there is a way to do this without consolidating, especially since if statements can do it? trying to keep things organized better.

[MrProgrammer]

Hi, and welcome to the forum.

I'm running into too many IF statements …

IF is a function, not a statement. Calc has no statements.

… where in Excel I have no problem.

Then why not use Excel, if it better meets your needs? Certainly use the software that works best for you.

I'm not the most advance when it comes to spreadsheets …

[Tutorial] Ten concepts that every Calc user should know

You have allowed human readability to take precedence over computational functionality and are making this task very, very difficult.

Agreed. It would be best to arrange the data and calculations in simple tables with no formatting where you can use functions, pivot tables, filtering, sorting, and other Calc features. Once that is done you can then use additional sheets which present the formatted-to-death data in the way you prefer.

I'm not the most advance when it comes to spreadsheets (ie Excel), but I can't think of any other way to make make this work.

If you want to continue on the present difficult path see Easy way to make highly nested conditionals.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[keme]

... I was told there is a way to do this without consolidating, especially since if statements can do it? trying to keep things organized better.

I see two problems with this attitude, concerning the uploaded file:
1 - Some content is missing. You are referencing cells which seem to be part of a merged range (which means that the referenced data does not exist).
2 - Structure is not predictable. Some referenced data ranges in your formulas' "IF-sequences" skip a cell, while other data ranges are continuous cell ranges. In many cases, lookup key and return value is not in adjoining ranges (sometimes on different sheets. Sometimes column connected to row.)

(2) can most likely be worked around by using range joins, so 64 levels of nested IF() function calls can be reduced to 8 nested IF() and 16 LOOKUP(). If you separate data from presentation and consolidate each set of data to tabular form, as others have already suggested, you would most likely have needed one single VLOOKUP() to replace those nested IFs. At worst you'd need one IF() and two LOOKUP() or a combination of one or two MATCH() and one INDEX() function calls in each of your formulas.

(1) has most likely happened by mistake, because you don't have complete control of where the important data elements are stored. It may also be by design, but then it is a design I cannot understand. This is a problem when you work with a spreadsheet model over time, because you forget stuff. The problem is increased whenever you need other people to understand your data structure (like when you come here asking for help).

So, there are a few options.

• Go back to using Excel, which will allow larger cell formulas and deeper nesting than Calc.
• Change to LibreOffice which supports the IFS() function in Calc. Rework your formulas using IFS() to reduce the level of nesting.
• Rework your formulas to utilize lookup functions. This job may take a day, or perhaps a week, and can simplify your largest formulas by a factor of 2, roughly. More compatible with open source software, and easier to debug.
• Rework your data structure, and change your formulas accordingly. This job may take a day or three, but requires that you understand the distinction between data (stored, searchable info) and presentation (labels, input and output forms). This can simplify your largest (and most important) formulas by a factor of 50-100. Makes everyting easier to debug, and workable when you need to extend your roadmap or you need other people to work on this.