Formula for Logical Text Function?

Discuss the spreadsheet application
Post Reply
tookiebrookie
Posts: 5
Joined: Tue Apr 15, 2008 2:31 am

Formula for Logical Text Function?

Post by tookiebrookie »

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.
User avatar
kingfisher
Volunteer
Posts: 2123
Joined: Tue Nov 20, 2007 10:53 am

Re: Formula for Logical Text Function?

Post by kingfisher »

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.9 on Linux
tookiebrookie
Posts: 5
Joined: Tue Apr 15, 2008 2:31 am

Re: Formula for Logical Text Function?

Post by tookiebrookie »

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?
User avatar
kingfisher
Volunteer
Posts: 2123
Joined: Tue Nov 20, 2007 10:53 am

Re: Formula for Logical Text Function?

Post by kingfisher »

Sorry, I forgot the = which must precede any formula. Bad habit. :oops:
Apache OpenOffice 4.1.9 on Linux
tookiebrookie
Posts: 5
Joined: Tue Apr 15, 2008 2:31 am

Re: Formula for Logical Text Function?

Post by tookiebrookie »

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.
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Formula for Logical Text Function?

Post by keme »

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.
Apache OO 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
User avatar
kingfisher
Volunteer
Posts: 2123
Joined: Tue Nov 20, 2007 10:53 am

Re: Formula for Logical Text Function?

Post by kingfisher »

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.9 on Linux
tookiebrookie
Posts: 5
Joined: Tue Apr 15, 2008 2:31 am

Re: Formula for Logical Text Function?

Post by tookiebrookie »

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?
User avatar
kingfisher
Volunteer
Posts: 2123
Joined: Tue Nov 20, 2007 10:53 am

Re: Formula for Logical Text Function?

Post by kingfisher »

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.9 on Linux
Post Reply