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)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
  • 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 '18 at 15:49
  • 1
    last line reminds me of a joke – kmdreko Apr 3 '18 at 1:02
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))
  • 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 '18 at 12:55
  • Use VBA and create a user-defined function. – user1750995 Apr 2 '18 at 2:08
  • A bit sad that this skips the general question and only solves for this particular example. – pipe Apr 3 '18 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 '18 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 '18 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!!

  • 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 '18 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 '18 at 20:00
  • TRUE is WAHR and FALSE is FALSCH. – IQV Apr 3 '18 at 13:28
  • And why a downvote? – teylyn Apr 3 '18 at 20:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.