Multiple IF Statements

Discuss the spreadsheet application

Multiple IF Statements

Postby crbenn01 » Tue Mar 24, 2020 8:15 pm

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.
Attachments
competency_help.ods
(88.64 KiB) Downloaded 20 times
OpenOffice 4.1.4.2
crbenn01
 
Posts: 2
Joined: Tue Mar 24, 2020 8:11 pm

Re: Multiple IF Statements

Postby FJCC » Tue Mar 24, 2020 9:25 pm

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   Expand viewCollapse view
=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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7680
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Multiple IF Statements

Postby crbenn01 » Tue Mar 24, 2020 9:50 pm

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.
OpenOffice 4.1.4.2
crbenn01
 
Posts: 2
Joined: Tue Mar 24, 2020 8:11 pm

Re: Multiple IF Statements

Postby MrProgrammer » Tue Mar 24, 2020 11:17 pm

Hi, and welcome to the forum.

crbenn01 wrote:I'm running into too many IF statements …
IF is a function, not a statement. Calc has no statements.

crbenn01 wrote:… 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.

crbenn01 wrote:I'm not the most advance when it comes to spreadsheets …
[Tutorial] Ten concepts that every Calc user should know

FJCC wrote: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.

crbenn01 wrote: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.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3982
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Multiple IF Statements

Postby keme » Wed Mar 25, 2020 9:13 am

crbenn01 wrote:... 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.
User avatar
keme
Volunteer
 
Posts: 3358
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway


Return to Calc

Who is online

Users browsing this forum: Zizi64 and 3 guests