## Formula with 3 possible outcomes

### Formula with 3 possible outcomes

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!
OOo: OpenOffice.org 4.1.8 (English)
OS: Windows 10, 64-bit (English)

AylaAsperger

Posts: 18
Joined: Sat May 17, 2008 10:00 am
Location: The Netherlands

### Re: Formula with 3 possible outcomes

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

=VLOOKUP(Points;HelperTable;2)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 29107
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Formula with 3 possible outcomes

Villeroy wrote: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.....
OOo: OpenOffice.org 4.1.8 (English)
OS: Windows 10, 64-bit (English)

AylaAsperger

Posts: 18
Joined: Sat May 17, 2008 10:00 am
Location: The Netherlands

### Re: Formula with 3 possible outcomes

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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 29107
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Formula with 3 possible outcomes

Villeroy wrote: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!
OOo: OpenOffice.org 4.1.8 (English)
OS: Windows 10, 64-bit (English)

AylaAsperger

Posts: 18
Joined: Sat May 17, 2008 10:00 am
Location: The Netherlands

### Re: Formula with 3 possible outcomes

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 29107
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Formula with 3 possible outcomes

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

AylaAsperger wrote: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.
OOo: OpenOffice.org 4.1.8 (English)
OS: Windows 10, 64-bit (English)

AylaAsperger

Posts: 18
Joined: Sat May 17, 2008 10:00 am
Location: The Netherlands

### Re: Formula with 3 possible outcomes

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 29107
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Formula with 3 possible outcomes

Villeroy wrote: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.
OOo: OpenOffice.org 4.1.8 (English)
OS: Windows 10, 64-bit (English)

AylaAsperger

Posts: 18
Joined: Sat May 17, 2008 10:00 am
Location: The Netherlands