Formula with 3 possible outcomes

[AylaAsperger]

Hello,

Is it possible to build a formula that has 3 possible outcomes depending on the outcome of a certain sum. I tried to build something like an IF inside another IF but it always gives the third option, no matter what the value is. It has to work something like this, if a number in a specified cell is below 10 give "A", if its between 11 and 20 give "B", if its between 20 and 30, give "C".

I hope anybody can help me out with this!

[Villeroy]

10 A
20 B
30 C
... as many as you like, ordered by first column.

=VLOOKUP(Points;HelperTable;2)

[AylaAsperger]

10 A
20 B
30 C
... as many as you like, ordered by first column.

=VLOOKUP(Points;HelperTable;2)

And in this case, when 1,2,3,4,5,6,7,8,9,10 all lead to the same answer, do I have to put them in all 10? Or is there a way to say, from 1 to 10 - A.

And what could VLOOKUP be in the dutch version of OOo?

By the way, I think it would be a lot handier if translations of OOo would stick to the english codes for formulas.....

[Villeroy]

In the catalog of functions there is a category named "Spreadsheet" with functions V(ertical)LOOKUP, H(orizontal)LOOKUP, LOOKUP, MATCH,...
In German VLOOKUP is SVERWEIS.
See help and/or function wizard (Ctrl+F2)

A macro to use English functions with all locales: http://www.oooforum.org/forum/viewtopic.phtml?t=23270

[AylaAsperger]

In the catalog of functions there is a category named "Spreadsheet" with functions V(ertical)LOOKUP, H(orizontal)LOOKUP, LOOKUP, MATCH,...
In German VLOOKUP is SVERWEIS.
See help and/or function wizard (Ctrl+F2)

A macro to use English functions with all locales: http://www.oooforum.org/forum/viewtopic.phtml?t=23270

I dont know how to use macros. I have been scrolling the help file to find the dutch version of these LOOKUP formulas but I havent found it yet. Since Dutch and German are very different SVERWEIS will probably not work either. Anyways I will keep on looking. Thanks for the tip!

[Villeroy]

http://cherbe.free.fr/traduc_fonctions_xl97.html
VLOOKUP ... VERT.ZOEKEN

[AylaAsperger]

In the catalog of functions there is a category named "Spreadsheet" with functions V(ertical)LOOKUP, H(orizontal)LOOKUP, LOOKUP, MATCH,...

And in this case, when 1,2,3,4,5,6,7,8,9,10 all lead to the same answer, do I have to put them in all 10? Or is there a way to say, from 1 to 10 - A.

Thank you very much for all your help. I have used VLOOKUP and it works perfectly! As to my problem with multiple numbers pointing to the same value, I just put the lowest number that leads to that value in the cell left to it. Because in the help I saw that if it is a sorted row, and it cant find the given value, it wil give the highest one that is still below the number.

[Villeroy]

Yes, you got the basics. VLOOKUP, HLOOKUP and MATCH know an unordered mode as well. Just add a last argument 0 or FALSE, e.g. VLOOKUP(23;range;2;0)
This looks up the exact value of 23 without the requirement of the first column beeing sorted.

[AylaAsperger]

Yes, you got the basics. VLOOKUP, HLOOKUP and MATCH know an unordered mode as well. Just add a last argument 0 or FALSE, e.g. VLOOKUP(23;range;2;0)
This looks up the exact value of 23 without the requirement of the first column beeing sorted.

I saw that indeed. But I have 3 values for the numbers 1 to 40. Now I only had to put in 1, 17 and 32, and it worked perfectly. In the situation where I would use the exact value I would have to make al 40 rows, meaning much duplicate values. But I can see it would come in handy in other situations!

The formula I use now is : =ALS(A4="";"";(VERT.ZOEKEN(A4;A20:B22;2))) so that it will only fill in a value if A4 is not empty, and choosing the value for 1-16, 17-31 or 32-40.