Had too many if's, moving to switch, how to nest?

English
#1

Hit the function limit in calc
I have a number of cells that autofill based on data from 4 other cells

The sheet is used in a tabletop RPG to develop weapons
User determines: class (7), subclass (3 or 4), type (2), and sometimes template (2)

What I started with (I’ve broken them down to make it easier to follow the nesting):
=IF(B3=“pistol”,
IF(B4=“holdout”,(INDEX(V5:V33,IF(B5=“plasma”,B2-1,B2-17),1)),
IF(B4=“light”,(INDEX(W5:W33,IF(B5=“plasma”,B2-1,B2-17),1)),
IF(B4=“medium”,(INDEX(X5:X33,IF(B5=“plasma”,B2-1,B2-17),1)),
IF(B4=“heavy”,(INDEX(Y5:Y33,IF(B5=“plasma”,B2-1,B2-17),1)),“Subclass”)))),

IF(B3=“subassault”,
IF(B4=“light”,(INDEX(Z5:Z33,IF(B5=“plasma”,B2-1,B2-17),1)),
IF(B4=“medium”,(INDEX(AA5:AA33,IF(B5=“plasma”,B2-1,B2-17),1)),
IF(B4=“heavy”,(INDEX(AB5:AB33,IF(B5=“plasma”,B2-1,B2-17),1)),“Subclass”))),

IF(B3=“hunting”,
IF(B4=“light”,(INDEX(AC5:AC33,IF(B5=“plasma”,B2-1,B2-17),1)),
IF(B4=“medium”,(INDEX(AD5:AD33,IF(B5=“plasma”,B2-1,B2-17),1)),
IF(B4=“heavy”,(INDEX(AE5:AE33,IF(B5=“plasma”,B2-1,B2-17),1)),“Subclass”))),

IF(B3=“assault”,
IF(B4=“light”,(INDEX(AC5:AC33,IF(B5=“plasma”,B2-1,B2-17),1)),
IF(B4=“medium”,(INDEX(AD5:AD33,IF(B5=“plasma”,B2-1,B2-17),1)),
IF(B4=“heavy”,(INDEX(AE5:AE33,IF(B5=“plasma”,B2-1,B2-17),1)),“Subclass”))),

IF(B3=“support”,
IF(B4=“light”,(INDEX(AF5:AF33,IF(B5=“plasma”,B2-1,B2-17),1)),
IF(B4=“medium”,(INDEX(AG5:AG33,IF(B5=“plasma”,B2-1,B2-17),1)),
IF(B4=“heavy”,(INDEX(AH5:AH33,IF(B5=“plasma”,B2-1,B2-17),1)),“Subclass”))),

IF(B3=“sniping”,
IF(B4=“light”,(INDEX(AC5:AC33,IF(B5=“plasma”,B2-1,B2-17),1)),
IF(B4=“medium”,
IF(B6=“assault”,(INDEX(AD5:AD33,IF(B5=“plasma”,B2-1,B2-17),1)),
IF(B6=“support”,(INDEX(AG5:AG33,IF(B5=“plasma”,B2-1,B2-17),1)),“Template”)),
IF(B4=“heavy”,
IF(B6=“medium”,(INDEX(AG5:AG33,IF(B5=“plasma”,B2-1,B2-17),1)),
IF(B6=“heavy”,(INDEX(AH5:AH33,IF(B5"plasma",B2-1,B2-17),1)),“Template”)),“Subclass”))),

IF(B3=“sonic stunner”,“1”,“Subclass”),“Class”)))))))

I’ve started converting them to switches, but I’m not familiar with implementing switches; is there a method to nesting them?

So each section will be like this:
=SWITCH(B3=“pistol”,B4=“holdout”,INDEX(V5:V33,IF(B5=“plasma”,B2-1,B2-17),1),B4=“light”,INDEX(W5:W33,IF(B5=“plasma”,B2-1,B2-17),1),B4=“medium”,INDEX(X5:X33,IF(B5=“plasma”,B2-1,B2-17),1),IF(B4=“heavy”,INDEX(Y5:Y33,IF(B5=“plasma”,B2-1,B2-17),1),“Subclass”)

#2

Welcome!
It seems to me that you will greatly simplify the formula and the process of choosing a weapon if you approach the problem from a different angle. If you collect all possible options in a flat table with five columns class, subclass, type, template and weapon and 7 * 4 * 2 * 2=112 rows, then the search for the desired weapon can be implemented with one function DGET()

3 Likes
#3

I’ll give it a go, not sure if it’ll work right, or if I’ll have to double up the array calls… I’m actually cheating across two different tables in the index calls, but if it saves function calls I’ll give it a shot.

#4

IFS

1 Like
#5

with respect, I understand that you are trying to help, but I obviously understand how to construct IFS. That is not my problem, my problem is that I have too many function calls in one statement.

It would have been more helpful had you pointed to say: dget(), custom datatypes, switches, or some other, actual, solution; instead of implying that I did not understand the basics.

EDIT:
I need to apologize, whether or not I intended to come across the way I did, I did post what I did. I do appreciate that you gave an answer, and I do not want you to stop providing them. I let myself get a bit ruffled, and I shouldn’t have, I should have waited to respond so that I could get my ego out of the way.

Why not just delete it? because, in my opinion, that’s the easy way, the way of someone who isn’t really sorry. I do strive to be better, but I fail sometimes, and I am sorry that I failed to find a better way.

3 Likes
#6

Obviously? Is this information imprinted in the human DNA? All people in this world don’t understand basics of something (and that is OK). Most users coming to the site don’t understand the basics of the applications they use (and that is OK, too). And answerers should not assume any level of knowledge in the asker, beside what was explicitly stated by them. Exactly not assuming advanced knowledge helps to avoid silly situations, when some “obvious” overlooked solutions are not suggested because they are assumed known.
And of course, replies like “I understand that you are trying to help, but you better do this and that” from someone coming to get help and getting a response that took a volunteer answerer’s time and effort, are better kept unsaid.
“Oh, thanks - I see I wasn’t clear enough, my problem is specifically this” is much better response (and best, when the clarification of the question happens by editing it, so that people don’t need to read all the comments to understand what the author intended to ask in the first place).

#7

yeah, but to @NanoEther 's credit :

maybe you guys can follow up in PM … :innocent:
https://ask.libreoffice.org/faq#civilized

2 Likes
#8

Thank you Pierre, I was going to go over the edge, but I will clean up my response.

1 Like
#9

This is an attempt to rewrite the formula using IFS, as @LeroyG suggested:

=IFS(B3="pistol",                      IFS(B4="holdout",(INDEX(V5:V33,  IF(B5="plasma",B2-1,B2-17),1)),
                                           B4="light",  (INDEX(W5:W33,  IF(B5="plasma",B2-1,B2-17),1)),
                                           B4="medium", (INDEX(X5:X33,  IF(B5="plasma",B2-1,B2-17),1)),
                                           B4="heavy",  (INDEX(Y5:Y33,  IF(B5="plasma",B2-1,B2-17),1)),
                                           TRUE(),      "Subclass"),
     B3="subassault",                  IFS(B4="light",  (INDEX(Z5:Z33,  IF(B5="plasma",B2-1,B2-17),1)),
                                           B4="medium", (INDEX(AA5:AA33,IF(B5="plasma",B2-1,B2-17),1)),
                                           B4="heavy",  (INDEX(AB5:AB33,IF(B5="plasma",B2-1,B2-17),1)),
                                           TRUE(),      "Subclass"),
     OR(B3="hunting",B3="assault"),    IFS(B4="light",  (INDEX(AC5:AC33,IF(B5="plasma",B2-1,B2-17),1)),
                                           B4="medium", (INDEX(AD5:AD33,IF(B5="plasma",B2-1,B2-17),1)),
                                           B4="heavy",  (INDEX(AE5:AE33,IF(B5="plasma",B2-1,B2-17),1)),
                                           TRUE(),      "Subclass"),
     B3="support",                     IFS(B4="light",  (INDEX(AF5:AF33,IF(B5="plasma",B2-1,B2-17),1)),
                                           B4="medium", (INDEX(AG5:AG33,IF(B5="plasma",B2-1,B2-17),1)),
                                           B4="heavy",  (INDEX(AH5:AH33,IF(B5="plasma",B2-1,B2-17),1)),
                                           TRUE(),      "Subclass"),
     B3="sniping",                     IFS(B4="light",  (INDEX(AC5:AC33,IF(B5="plasma",B2-1,B2-17),1)),
                                           B4="medium", IFS(B6="assault",(INDEX(AD5:AD33,IF(B5="plasma",B2-1,B2-17),1)),
                                                            B6="support",(INDEX(AG5:AG33,IF(B5="plasma",B2-1,B2-17),1)),
                                                            TRUE(),      "Template"),
                                           B4="heavy",  IFS(B6="medium",(INDEX(AG5:AG33,IF(B5="plasma",B2-1,B2-17),1)),
                                                            B6="heavy", (INDEX(AH5:AH33,IF(B5="plasma",B2-1,B2-17),1)),
                                                            TRUE(),     "Template"),
                                           TRUE(),      "Subclass"),
     B3="sonic stunner",               "1",
     TRUE(),                           "Subclass")

And then possibly you can consider re-thinking the layout of the data, like in the following spreadsheet:

weapons.ods (13.1 KB)

It allows to simplify the formula in the yellow box, using VLOOKUP on an intermediate lookup table.

(the spreadsheet doesn’t handle “Template”, sorry; hope that the idea is clear)

2 Likes
#10

Thank you for your time, your response, and the discussion.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.