[Solved] Extend logic formula from 16 conditions to 21

[Canada]

I use this formula for years without any problems:

=WENN(R28=1;Y28;WENN(R28=2;Y29;WENN(R28=3;Y30;WENN(R28=4;Y31;WENN(R28=5;Y32;WENN(R28=6;Y33;WENN(R28=7;Y34;WENN(R28=8;Y35;WENN(R28=9;Y36;WENN(R28=10;Y37;WENN(R28=11;Y38;WENN(R28=12;Y39;WENN(R28=13;Y40;WENN(R28=14;Y41;WENN(R28=15;Y42;WENN(R28=16;Y43

Of course, the necessary numbers of brackets at the end of it is set.

Now I want to extend the formula with

WENN(R28=17;Y44;WENN(R28=18;Y45;WENN(R28=19;Y46;WENN(R28=20;Y47;WENN(R28=21;Y48;Y49)))))))))))))))))))))

But after completing the former formula with these new details, it doesn't work any more.

The new complete formula reads as follows:

=WENN(R28=1;Y28;WENN(R28=2;Y29;WENN(R28=3;Y30;WENN(R28=4;Y31;WENN(R28=5;Y32;WENN(R28=6;Y33;WENN(R28=7;Y34;WENN(R28=8;Y35;WENN(R28=9;Y36;WENN(R28=10;Y37;WENN(R28=11;Y38;WENN(R28=12;Y39;WENN(R28=13;Y40;WENN(R28=14;Y41;WENN(R28=15;Y42;WENN(R28=16;Y43;WENN(R28=17;Y44;WENN(R28=18;Y45;WENN(R28=19;Y46;WENN(R28=20;Y47;WENN(R28=21;Y48;Y49)))))))))))))))))))))

Note: the German word "Wenn" means "IF". Is there a limitation, how long such a formula can be ?

Any ideas ?

Gustav

[Villeroy]

=OFFSET(Y27;R28;0)
=VERSCHIEBUNG(Y27;R28;0)
works with any number in R28.

[Canada]

Following a screenshot of the table the formula is used with. As I already wrote: the original formula worked perfectly since installing. So why change it ? I do not see how the formula extension can amend its usefulness ?



Next conversation in German ?

Edit: This is an English-speaking forum. Switching to German will cause the topic to be locked. -- MrProgrammer, forum moderator

Gustav

[FJCC]

The formula in the original post works for me, as shown in the attached file. I put the formula in Q28.
Villeroy's OFFSET formula is a much better approach.

[Villeroy]

As I already wrote: the original formula worked perfectly since installing. So why change it ? I do not see how the formula extension can amend its usefulness ?

Because it needs to be maintained while becoming more and more unmaintainable as it grows. QED.

[Canada]

Thanks, but what means "maintainable" ? I use this program for 5 months every year. This season is the first I had to enlarge it. Do you consider 21 mathematical work steps as too many for a single task ?

Even so, I made a copy of the original data sheet, deleted the formula and put in the necessary details again, one by one, starting from scratch, so to speak. Doesn't work, either.

Anyway, already thanks for your earlier posts.

Gustav

[Villeroy]

You could copy the 21 nested IFs from the document provided by FJCC.
Yes, 21 steps are 20 too many for such a trivial task.

[Canada]

To FJCC

Sorry, I totally overlooked your post. Right now I'm in a hurry. Will check your suggestion after my return.

Gustav

[Canada]

None of the hints worked on my mask, albeit FJCC's excel sheet formula is exactly the same I used on my mask. It's late now - I'll continue tomorrow and'll call again. Thanks for now.

Gustav

[robleyd]

Would VLOOKUP not be a simpler solution?

[Canada]

Now I'm at a loss; what is VLOOKUP, please.

Besides, the formula in my mask worked perfectly - until I tried to add new connections. I'm under the impression that something happened to the data sheet, albeit every other formula (and there are many on this sheet) works fine.
But, as I said - tomorrow's another day.

Gustav

[robleyd]

Help - F1 and search VLOOKUP. There is a tutorial in the Calc section of the tutorials here.

[Mountaineer]

Now I'm at a loss; what is VLOOKUP, please.

Gustav

Check SVERWEIS() in german language.

On one of your earlier questions: There is a max lenght and complexity. But there is also an error-code, if this applies.

In my opinion you should switch to villeroys formula,
but you never told how did you detect does't work:

But after completing the former formula with these new details, it doesn't work any more.

Did you get an error code, not the expected value or
no computation at all?

J.

PS: If you have to much problems translating functions like VLOOKUP:
You can set calc to use english names for functions, if you like.
There are forums in german language like
https://de.openoffice.info/

[Canada]

I'm sorry to report that villeroys suggestion doesn't work. My code nos. from 1 - 21 don't work anymore once I use his formula. When, i.e., the first entry is 14 for "food", the next description is automatically that for code no. 15, and so on, no matter, what code I actually insert in column R on the relevant line.

And no, I don't get any error message when enlarging my initial formula; it just happens, that the beginning of it is shown in the cell, but no action like showing the content of the connected cell.

I checked VLOOKUP,/SVERWEIS and it might be a solution, but it'll make it necessary to change the entire mask and re-entry all details already being entered.

What I don't understand is, why, except, if it is too long, I cannot just add the few new formula details to the existing ones. Everything else on this mask, which is connected with different tables and many more formulas, works perfectly. But even so, has anyone an idea about any possible sudden corruption of the formula or the spreadsheet in general ?

[FJCC]

Please post a simple file showing the problem. All we need is the cell containing the failing formula and the column from which you are trying to pull data. We need the actual spreadsheet file, not an image. Thanks!

[Villeroy]

=OFFSET($Y$27;R28;0)
=VERSCHIEBUNG($Y$27;R28;0)
with absolute reference to Y27.

[Canada]

I uploaded my file to OneDrive, but had to change from OO to Excel first, for OneDrive cannot open OO files. I checked the result, and it's not different to my original file. This way now you can check the entire document, albeit all inks to other related tables, not relevant with the problem on hand, are disrupted now.

https://1drv.ms/x/s!AshdRQuw07vVhmw-or1 ... t?e=BeOCin

I'm really curious, if you can find what makes it impossible to enlarge the initial formula. Thanks for your help.

Gustav

[FJCC]

I downloaded your file as an ods (because Dropbox had that option) and I modified two cells, Q29 and Q30. In Q29 I put the nested IF() formula from your first post that you described as "the new complete formula" and in Q30 I put the most recent version of Villeroy's OFFSET() function. In both cases, I modified the formulas to reference the appropriate row of column R, either 29 or 30. Both formulas work for me.

[Canada]

To fjcc

Hi, thanks for your efforts. But - incl. code 16, my formula already worked, but I want to enlarge it by the formulas also shown in my initial post. And that's, what doesn't work.

[FJCC]

I do not understand what you are seeing that causes you to say that the formulas do not work. If in Y48 I write "New Item", I can then enter 21 in either R29 or R30 and the corresponding cell in column Q displays "New Item". That works just as I expect it to. Please explain the inputs you are using, the output you are seeing and what you would expect to see.

[Canada]

O.k., now I refer solely to the table you posted today at 10:53. I understand, that's the one you work with and which shows the relevant linked text once you enter the code in column R. I downloaded the table and tried the very same, but to no avail. When I enter code 16, (the last covered by the formula) the correct text from cell Y43 is displayed in the correct cell in column Q. Even when I enter 17 in column R, the right text appears in Q, but only, because it's the last alternative to 16. Any code input in R >16 has no effect.

And I don't wonder why, for after the formula for code 16 - there's no more. How come, you can input code 21 and whatever text you inserted in Y48 appears in Q, when there's no formula in Q, linking any cell in Y with a code higher than 16 ?

[Villeroy]

We can not answer any of your questions until you upload a document demonstrating the problem.

[Canada]

download/file.php?id=42909

As I already wrote: I uploaded my file earlier today and jfcc incl. it in his post. Please open this link. It's a true copy of my file, and check it accordingly with my last post.

[FJCC]

The file G & V 20 - 21 excerpt_fjcc.ods that I uploaded today has formulas in Q29 and Q30 that do look for information farther down column Y. Q29 has the nested-IF() version that accepts values up to 21 in column R. Q30 has the OFFSET() version of the formula and it will go down column Y to the last row of the spreadsheet. Enter some text in Y128, put 101 in R30 and the appropriate text will appear in Q30.

[Villeroy]

Various formulas (IF, OFFSET, INDEX, LOOKUP) yielding the same result.

[Canada]

To fjcc:

It doesn't work.

Here's what happens:

Q28 still works with the initial formula, means, it accepts only codes up to 16 and displays the proper text from the list.
Q29 works up to Y22.
Q30 works limitless, everything's o.k., as you described.
Q31 works as before, up to 16, from 17 on it displays Y20, no matter the inserted code.
Q32 works as before, up to 16, from 17 on it displays Y21, no matter the inserted code.
Q33 works as before, up to 16, from 17 on it displays Y22, no matter the inserted code.
Q34 works as before, up to 16, from 17 on it displays 0, no matter the inserted code, and so do all following cells in column Q.


To villeroy:

It works. I have copied cells/formulas from column AE from your upload to column Q in my table and can use codes and related links higher than 16.

Many thanks to both of you. I appreciate your efforts very much. Both of you were a great help.

But one last question:

my formula worked so well for a long time. Do you have any idea why I cannot enlarge it the same way I developed it in the first place ?

Best regards

Gustav

[FJCC]

The formulas in Q29 and Q30 are the only ones that work because they are the only ones I changed. I tried to make that clear in an earlier post but I see that I failed.

The formula in Q29 is the formula you originally posted. All I changed was the references to R29, so it would work in Q29 and I changed WENN to IF so it would work in my English locale.

[Canada]

To fjcc:

Obviously I didn't understand your explanations correctly. For whatever reason, I assumed that the problem solution you created would apply to all cells. Please excuse my misunderstanding. Of course, just to copy the correct formula to all other relevant cells in column Q would have done the trick, too.

Thanks again for your help.

best regards

Gustav
New Brunswick/Canada