Formula for Logical Text Function?

Discuss the spreadsheet application

Formula for Logical Text Function?

Postby tookiebrookie » Thu Apr 17, 2008 6:02 am

In Calc, how do I say that if a cell reference contains certain text, then result with text "X", otherwise, leave it blank?

Example: If cell B2 contains the name "Tom", put an "X" in the current cell.

Here's what I tried that DOESN'T work: In the current cell the formula is in, (cell E2): IF(B2=TOM;"X").

When do I have to use quotation marks? Should TOM be "TOM"?
Is it case sensative, or can I make it give a result whether it is Tom, TOM, or tom?
If I want the result to be an X or a checkmark, do there have to be quotes?
if the cell has any other name, do I have to tell it to leave the cell blank, or will it do so automatically?
How would I write it if I wanted the result to be 'Y' if the referenced cell contains the specific text, or 'N' if it doesn't?

What should I read to find out more about this kind of thing? Logical Functions seems to contain information about numeric data. Text functions seems to contain information about converting text to numbers. Is the information I'm looking for in Boolean arguments? Please help.
tookiebrookie
 
Posts: 5
Joined: Tue Apr 15, 2008 2:31 am

Re: Formula for Logical Text Function?

Postby kingfisher » Thu Apr 17, 2008 6:50 am

So many questions, forgive me if I miss one or more.

Your formula should be =IF(B2="Tom";"X";"")

That, I believe answers a couple of questions. That formula will produce an empty string if the searched data is not present.

You get more information from the software help (F1). For case sensitivity, see the menu Tools >Options >oo.o Calc >Calculate : "Case sensitive"; I wouldn't swear that makes a difference but suggest you try it. In your case, you don't want that option so de-select it.

 Edit: Amended formula by showing = sign and displaying in colour. 
Last edited by kingfisher on Thu Apr 17, 2008 8:48 am, edited 1 time in total.
Apache OpenOffice 4.1.1 on PCLinuxOS
User avatar
kingfisher
Volunteer
 
Posts: 2024
Joined: Tue Nov 20, 2007 10:53 am

Re: Formula for Logical Text Function?

Postby tookiebrookie » Thu Apr 17, 2008 7:59 am

Thanks for the quick response.

When I entered your suggestion into the function tool bar, the formula I entered showed up in the cell (not the results). I tried recalculating. What could be wrong?
tookiebrookie
 
Posts: 5
Joined: Tue Apr 15, 2008 2:31 am

Re: Formula for Logical Text Function?

Postby kingfisher » Thu Apr 17, 2008 8:47 am

Sorry, I forgot the = which must precede any formula. Bad habit. :oops:
Apache OpenOffice 4.1.1 on PCLinuxOS
User avatar
kingfisher
Volunteer
 
Posts: 2024
Joined: Tue Nov 20, 2007 10:53 am

Re: Formula for Logical Text Function?

Postby tookiebrookie » Thu Apr 17, 2008 9:05 am

Same problem. Formula displayed in cells, not results. Is it something in the formatting?

Did you try this in Calc yourself? Just put some text in a cell and in the next row try your formula with the text you entered.

Let me know if it works.
tookiebrookie
 
Posts: 5
Joined: Tue Apr 15, 2008 2:31 am

Re: Formula for Logical Text Function?

Postby keme » Thu Apr 17, 2008 10:06 am

At least two things can cause this:

Calc is set up to display formulas instead of results.
See menu Tools - Options, branch OpenOffice.calc - View. Make sure Display - Formulas is unticked.

Calc interprets the entered formula as text
Did you by any chance insert other characters than indicated, like a space before the equals sign? (This can easily happen if you copy/paste from a web browser.) Any input that doesn't start with an equals sign is taken as literal data, interpreted as numerical data if possible, and if no numerical interpretation is possible it is taken as text.

It may be a different language version of OOo too, where the IF() function is translated, but in that case you should get the #NAME? error code.
OOo 3.3.0 and Apache OOo 3.4.1/4.1.1, on Ms Windows 7/8 and Mac OS-X.
User avatar
keme
Volunteer
 
Posts: 1823
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Formula for Logical Text Function?

Postby kingfisher » Thu Apr 17, 2008 12:13 pm

If I copy and paste the formula, I get the same effect, which is very discombobulating. Delete what you have there now, use Format >Default formatting to re-set the cell format, then type the formula (i.e. don't paste).
Apache OpenOffice 4.1.1 on PCLinuxOS
User avatar
kingfisher
Volunteer
 
Posts: 2024
Joined: Tue Nov 20, 2007 10:53 am

Re: Formula for Logical Text Function?

Postby tookiebrookie » Fri Apr 18, 2008 5:00 am

The formula works now. Thanks.

Now, I am using VLOOKUP. When the cell referenced in the search criterion is blank, the cell with the function shows "#N/A". In Tools, I unticked the display formula option.

I have copied the function into a column. I will enter information in the criterion-referenced cells in the future. Meanwhile, I don't want the annoying error to keep showing up. How do I hide it?


Also, in a function "$" means absoulte reference, right? As in "$B5" always means that cell, so if you copy the formula into other cells, it is not a relative reference, but absolute?
tookiebrookie
 
Posts: 5
Joined: Tue Apr 15, 2008 2:31 am

Re: Formula for Logical Text Function?

Postby kingfisher » Fri Apr 18, 2008 6:39 am

One way to hide an #NA result is to use ISNA with IF: =IF(ISNA(VLOOKUP(<etc>));"":VLOOKUP(<etc>)) If you want to reverse the order of the results, =IF(NOT(ISNA(VLOOKUP(<etc>)));VLOOKUP(<etc>);"")

In the example you give, the $ makes only the column absolute. If you want the cell reference to be absolute, use $B$5; IOW put a $ in front of the row number as well as the column letter.
Apache OpenOffice 4.1.1 on PCLinuxOS
User avatar
kingfisher
Volunteer
 
Posts: 2024
Joined: Tue Nov 20, 2007 10:53 am


Return to Calc

Who is online

Users browsing this forum: Yahoo [Bot] and 29 guests