Formula with 3 possible outcomes

Discuss the spreadsheet application

Formula with 3 possible outcomes

Postby AylaAsperger » Sun May 18, 2008 8:04 pm

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: Dutch OpenOffice.org 3.3
OS: Windows 7 (both 64-bit and 32-bit)
User avatar
AylaAsperger
 
Posts: 9
Joined: Sat May 17, 2008 10:00 am
Location: The Netherlands

Re: Formula with 3 possible outcomes

Postby Villeroy » Sun May 18, 2008 8:07 pm

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

Re: Formula with 3 possible outcomes

Postby AylaAsperger » Sun May 18, 2008 8:11 pm

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: Dutch OpenOffice.org 3.3
OS: Windows 7 (both 64-bit and 32-bit)
User avatar
AylaAsperger
 
Posts: 9
Joined: Sat May 17, 2008 10:00 am
Location: The Netherlands

Re: Formula with 3 possible outcomes

Postby Villeroy » Sun May 18, 2008 8:19 pm

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

Re: Formula with 3 possible outcomes

Postby AylaAsperger » Sun May 18, 2008 8:26 pm

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: Dutch OpenOffice.org 3.3
OS: Windows 7 (both 64-bit and 32-bit)
User avatar
AylaAsperger
 
Posts: 9
Joined: Sat May 17, 2008 10:00 am
Location: The Netherlands

Re: Formula with 3 possible outcomes

Postby Villeroy » Sun May 18, 2008 8:37 pm

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

Re: Formula with 3 possible outcomes

Postby AylaAsperger » Sun May 18, 2008 11:32 pm

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: Dutch OpenOffice.org 3.3
OS: Windows 7 (both 64-bit and 32-bit)
User avatar
AylaAsperger
 
Posts: 9
Joined: Sat May 17, 2008 10:00 am
Location: The Netherlands

Re: Formula with 3 possible outcomes

Postby Villeroy » Sun May 18, 2008 11:47 pm

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

Re: Formula with 3 possible outcomes

Postby AylaAsperger » Mon May 19, 2008 12:08 am

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: Dutch OpenOffice.org 3.3
OS: Windows 7 (both 64-bit and 32-bit)
User avatar
AylaAsperger
 
Posts: 9
Joined: Sat May 17, 2008 10:00 am
Location: The Netherlands


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 15 guests