Using IF with multiple non numerical values??

Discuss the spreadsheet application

Using IF with multiple non numerical values??

Postby Mad Ad » Tue Jun 17, 2008 8:46 pm

My eyes are going crossed trying to read history here, and I cant search for IF so Ill have to just come out and ask my question.

=IF(G3="XL";20;0) Works fine, put XL in the box, out pops 20, otherwise 0, but what if I have a range in that one cell? Such as M L XL? each with a different then value and no otherwise value?

How do i format this please?

Thanks a lot
Mad Ad
Posts: 5
Joined: Tue Jun 17, 2008 7:49 pm

Re: Using IF with multiple non numerical values??

Postby squenson » Tue Jun 17, 2008 9:06 pm

You could use the function FIND to find a string within another string But be careful, as finding the string "L" in "M XL" will return a positive find, so include an initial space character in your search string, and one at the beginning of the reference string. Something like:
=IF(ISERR(FIND(" L";" M XL";1));"Unknown size";"Size L")
LibreOffice on Ubuntu 14.04
User avatar
Posts: 1885
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

Re: Using IF with multiple non numerical values??

Postby keme » Tue Jun 17, 2008 9:41 pm

IF() is meant for a simple selection between two options. You can nest one IF() inside another for more complex selections, but there are better tools for that (if I'm reading you right here). I suggest you make it in a couple of steps:

First make a list of permissible values
Create a new sheet or rename a blank sheet to "Sizes" (I'm guessing that you want to use clothing sizes. Right click on the tab and select the insert or rename option.)
On that sheet, enter the sizes down column A:
XS, S, M, L, XL, XXL
In column B, enter the value to be returned for each selection.

Make your conversion from size to number
In another sheet, designate a cell to enter a value into.
Use the MATCH() function to determine the position of the item in the list.
Use the INDEX() function to pick the correct return value from the other list.

You can also use VLOOKUP() to return the value directly, or use CHOOSE() and enter the value list directly in the formula.
All the options mentioned are used in the attached file, which also uses data validation to ensure you enter a valid size.
(7.54 KiB) Downloaded 103 times
Apache OO 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice on Mepis Antix MX 14
User avatar
Posts: 3394
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Return to Calc

Who is online

Users browsing this forum: No registered users and 26 guests