Formula with 3 possible outcomes

Discuss the spreadsheet application
Post Reply
User avatar
AylaAsperger
Posts: 18
Joined: Sat May 17, 2008 10:00 am
Location: The Netherlands

Formula with 3 possible outcomes

Post by 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!
OOo: OpenOffice.org 4.1.8 (English)
OS: Windows 10, 64-bit (English)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Formula with 3 possible outcomes

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
AylaAsperger
Posts: 18
Joined: Sat May 17, 2008 10:00 am
Location: The Netherlands

Re: Formula with 3 possible outcomes

Post by AylaAsperger »

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)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Formula with 3 possible outcomes

Post by 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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
AylaAsperger
Posts: 18
Joined: Sat May 17, 2008 10:00 am
Location: The Netherlands

Re: Formula with 3 possible outcomes

Post by AylaAsperger »

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)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Formula with 3 possible outcomes

Post by Villeroy »

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
AylaAsperger
Posts: 18
Joined: Sat May 17, 2008 10:00 am
Location: The Netherlands

Re: Formula with 3 possible outcomes

Post by AylaAsperger »

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)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Formula with 3 possible outcomes

Post by 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
AylaAsperger
Posts: 18
Joined: Sat May 17, 2008 10:00 am
Location: The Netherlands

Re: Formula with 3 possible outcomes

Post by AylaAsperger »

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)
Post Reply