17

I wrote a simple formula to check the value of a cell. Unfortunately, the formula is too long for my Excel 2007 version. The error is:

German: Die angegebene Formel kann nicht eingegeben werden, da sie mehr als 64 Verschachtelungsebenen verwendet.

English: The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format

Any idea how to make the formula shorter?

"WENN" means "IF" in German.

   =WENN(N7>=100000000;0;
    WENN(N7>=99000000;1;
    WENN(N7>=98000000;2;
    WENN(N7>=97000000;3;
    WENN(N7>=96000000;4;
    WENN(N7>=95000000;5;
    WENN(N7>=94000000;6;
    WENN(N7>=93000000;7;
    WENN(N7>=92000000;8;
    WENN(N7>=91000000;9;
    WENN(N7>=90000000;10;
    WENN(N7>=89000000;11;
    WENN(N7>=88000000;12;
    WENN(N7>=87000000;13;
    WENN(N7>=86000000;14;
    WENN(N7>=85000000;15;
    WENN(N7>=84000000;16;
    WENN(N7>=83000000;17;
    WENN(N7>=82000000;18;
    WENN(N7>=81000000;19;
    WENN(N7>=80000000;20;
    WENN(N7>=79000000;21;
    WENN(N7>=78000000;22;
    WENN(N7>=77000000;23;
    WENN(N7>=76000000;24;
    WENN(N7>=75000000;25;
    WENN(N7>=74000000;26;
    WENN(N7>=73000000;27;
    WENN(N7>=72000000;28;
    WENN(N7>=71000000;29;
    WENN(N7>=70000000;30;
    WENN(N7>=69000000;31;
    WENN(N7>=68000000;32;
    WENN(N7>=67000000;33;
    WENN(N7>=66000000;34;
    WENN(N7>=65000000;35;
    WENN(N7>=64000000;36;
    WENN(N7>=63000000;37;
    WENN(N7>=62000000;38;
    WENN(N7>=61000000;39;
    WENN(N7>=60000000;40;
    WENN(N7>=59000000;41;
    WENN(N7>=58000000;42;
    WENN(N7>=57000000;43;
    WENN(N7>=56000000;44;
    WENN(N7>=55000000;45;
    WENN(N7>=54000000;46;
    WENN(N7>=53000000;47;
    WENN(N7>=52000000;48;
    WENN(N7>=51000000;49;
    WENN(N7>=50000000;50;
    WENN(N7>=49000000;51;
    WENN(N7>=48000000;52;
    WENN(N7>=47000000;53;
    WENN(N7>=46000000;54;
    WENN(N7>=45000000;55;
    WENN(N7>=44000000;56;
    WENN(N7>=43000000;57;
    WENN(N7>=42000000;58;
    WENN(N7>=41000000;59;
    WENN(N7>=40000000;60;
    WENN(N7>=39000000;61;
    WENN(N7>=38000000;62;
    WENN(N7>=37000000;63;
    WENN(N7>=36000000;64;
    WENN(N7>=35000000;65;
    WENN(N7>=34000000;66;
    WENN(N7>=33000000;67;
    WENN(N7>=32000000;68;
    WENN(N7>=31000000;69;
    WENN(N7>=30000000;70;
    WENN(N7>=29000000;71;
    WENN(N7>=28000000;72;
    WENN(N7>=27000000;73;
    WENN(N7>=26000000;74;
    WENN(N7>=25000000;75;
    WENN(N7>=24000000;76;
    WENN(N7>=23000000;77;
    WENN(N7>=22000000;78;
    WENN(N7>=21000000;79;
    WENN(N7>=20000000;80;
    WENN(N7>=19000000;81;
    WENN(N7>=18000000;82;
    WENN(N7>=17000000;83;
    WENN(N7>=16000000;84;
    WENN(N7>=15000000;85;
    WENN(N7>=14000000;86;
    WENN(N7>=13000000;87;
    WENN(N7>=12000000;88;
    WENN(N7>=11000000;89;
    WENN(N7>=10000000;90;
    WENN(N7>=9000000;91;
    WENN(N7>=8000000;92;
    WENN(N7>=7000000;93;
    WENN(N7>=6000000;94;
    WENN(N7>=5000000;95;
    WENN(N7>=4000000;96;
    WENN(N7>=3000000;97;
    WENN(N7>=2000000;98;
    WENN(N7>=1000000;99;
    WENN(N7>=500000;100;0)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
2
  • 13
    Your question would be much more readable if you'd have structured the formula (there's a pending edit regarding this) -- and, IMHO more important, if you describe in words want you are going to accomplish. Maybe we have a xy problem here.
    – mpy
    Apr 1, 2018 at 15:49
  • 1
    last line reminds me of a joke
    – kmdreko
    Apr 3, 2018 at 1:02

2 Answers 2

65

I think the formula is equivalent to:

=IF(OR(N7>=100000000,N7<500000),0,100-INT(N7/1000000))

Now that Kevin has reformatted the formula, I can see quite clearly that the formula is correct in all cases.

Thanks to the questioner's comment, I can state that the equivalent German formula is:

=WENN(ODER(N7>=100000000;N7<500000);0;100-GANZZAHL(N7/1000000))
5
  • Works awesome!!! thx - i used de.excel-translator.de/translator to translate the formula. In German it look like =WENN(ODER(N7>=100000000;N7<500000);0;100-GANZZAHL(N7/1000000))
    – evavienna
    Apr 1, 2018 at 12:55
  • Use VBA and create a user-defined function. Apr 2, 2018 at 2:08
  • A bit sad that this skips the general question and only solves for this particular example.
    – pipe
    Apr 3, 2018 at 13:05
  • Formula seems correct but the real answer is that this should be done within a lookup table like tevlyn says. Trying to do this with an If statement is bad practice.
    – WhatEvil
    Apr 3, 2018 at 14:15
  • 5
    @pipe, @WhatEvil - The questioner wanted to reduce the size of his IF statement, and because of the structure of his data, I gave the (to a mathematician) obvious answer and I completely disagree that it is in any sense incorrect or bad practice. Obviously a look-up table would be needed for a less linear function, but it is overkill for these data and it can hardly be considered optimal: a direct calculation is always better than a look-up when the latter can be avoided.
    – AFH
    Apr 3, 2018 at 15:20
21

Put all your threshold values into a range on a worksheet and use a lookup formula with an approximate match.

Sort your data ascending by value of the lookup column. The lookup column must be the first column of the lookup table if you want to use VLookup. If you are happy to use an Index/Match combo, the lookup column can be anywhere in the table.

The screenshot below has a sample lookup table in cells A1 to B6. The table is sorted ascending by the values in column A. This is important. If the table is not sorted ascending, then following formula will not work.

The value in cell E2 is not an exact match for any of the values in column A. The Vlookup formula will find the next smallest value (i.e. 50) and will return the result from column 2 of the lookup table for this approximate match. Note that for an approximate match the fourth parameter of VLookup must be "TRUE" or 1.

Here is the formula:

=VLOOKUP(E2,$A$1:$B$6,2,TRUE)

And the screenshot:

enter image description here

The German name for Vlookup is SVerweis (I think), and you need to replace commas with semicolons in German Excel. I don't know what "TRUE" is in German Excel, but you can use a 1 for TRUE and a 0 for FALSE.

=SVerweis(E2;$A$1:$B$6;2;1)

Hoffentlich klappt's!!

4
  • Ich hoffe auch! Let me read through this - looks like a lot of work for me. thx - i let you know if im able to make it work :)
    – evavienna
    Apr 1, 2018 at 10:13
  • Filling in table of numbers is not a lot of work if the numbers are spaced evenly. Enter the first two number, select them and drag the fill handle.
    – teylyn
    Apr 1, 2018 at 20:00
  • TRUE is WAHR and FALSE is FALSCH.
    – IQV
    Apr 3, 2018 at 13:28
  • And why a downvote?
    – teylyn
    Apr 3, 2018 at 20:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .