How can I test the text content of a cell

I wish to create a logical test - an IF statement that tests whether a cell 
contains H, M or L, and output the result as a number.
I cannot get the IF statement to recognise text as true or false - my Excel 
2007 gives an error when I try to do something like IF(E9=H ....) is there a 
way to do this?
0
Utf
3/16/2010 11:43:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
1394 Views

Similar Articles

[PageSpeed] 52

=IF(E9="H"


Gord Dibben  MS Excel MVP

On Tue, 16 Mar 2010 16:43:01 -0700, catefaulkes
<catefaulkes@discussions.microsoft.com> wrote:

>I wish to create a logical test - an IF statement that tests whether a cell 
>contains H, M or L, and output the result as a number.
>I cannot get the IF statement to recognise text as true or false - my Excel 
>2007 gives an error when I try to do something like IF(E9=H ....) is there a 
>way to do this?

0
Gord
3/16/2010 11:56:38 PM
For Excel to recognize text, you need to surround it in quotes, like this:
=if(or(e9="H",e9="M",e9="L"),true,false)

Regards,
Fred

"catefaulkes" <catefaulkes@discussions.microsoft.com> wrote in message 
news:9B0D27BB-4B91-4844-9200-F14C47DE1B45@microsoft.com...
>I wish to create a logical test - an IF statement that tests whether a cell
> contains H, M or L, and output the result as a number.
> I cannot get the IF statement to recognise text as true or false - my 
> Excel
> 2007 gives an error when I try to do something like IF(E9=H ....) is there 
> a
> way to do this? 

0
Fred
3/16/2010 11:56:53 PM
Hi,

You don't say if you want a different number for each of the text values or 
the same number so we'll start with the same number

=IF(OR(A1="H",A1="M",A1="L"),99999,"")


-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"catefaulkes" wrote:

> I wish to create a logical test - an IF statement that tests whether a cell 
> contains H, M or L, and output the result as a number.
> I cannot get the IF statement to recognise text as true or false - my Excel 
> 2007 gives an error when I try to do something like IF(E9=H ....) is there a 
> way to do this?
0
Utf
3/17/2010 12:08:07 AM
Hi catefaulkes,

Yet another approach:
=(E9="H")*1+(E9="M")*2+(E9="L")*3
Replace the 1,2,3 with whatever values you require. If no match occurs, 0 is returned.

-- 
Cheers
macropod
[Microsoft MVP - Word]


"catefaulkes" <catefaulkes@discussions.microsoft.com> wrote in message news:9B0D27BB-4B91-4844-9200-F14C47DE1B45@microsoft.com...
>I wish to create a logical test - an IF statement that tests whether a cell 
> contains H, M or L, and output the result as a number.
> I cannot get the IF statement to recognise text as true or false - my Excel 
> 2007 gives an error when I try to do something like IF(E9=H ....) is there a 
> way to do this?
0
macropod
3/17/2010 5:50:06 AM
Reply:

Similar Artilces:

can't download pop3 email
This is my error message Task Receiving reported error 0x800CCC0F The connection to the server was interrupted If this problem continues contact your server administrator or your ISP The server responded +OK How do I fix this? What did your ISP say? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After scratching one's head, M Pond <anonymous@discussions.microsoft.com> asked this group: | This is my error mes...

Search selected text
Hi, I would like to seacrh a word by selecting a word and pressing shift + f4. Is that possible? Thanks! I assume that you mean search a Word *document* for a selected word? In which case the default search shortcut is CTRL+F, which will present the search dialog with the selected word entered. Shift+F4 is assigned to RepeatFind -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ...

Right Click to Autofill cells in 2003
Hello All, In 97 to 2002 it was possible to right click when using Autofill to copy the cells down rather than continuing the sequence. in 2003 i just get a pretty red line (what purpose does this serve?). Does anyone know if I can get back the old functionality? Many thanks, Danny I have that functionality in Excel 2003 as well, maybe you have installed an add-in? In any case if you hold down ctrl while using the left click copy down it will also copy as opposed to fill a series Regards, peo sjoblom "DannyJ" wrote: > Hello All, > > In 97 to 2002 it was possi...

Text Alignment problem
Pub 07 Win XP. Aligning one line of text re-aligns all text in the textbox. If I want the first line left justified, the second centered and the third right justified, I can't do it. Do I really need separate text boxes? -- Charles Joseph Silk Purse Productions In Publisher 2000 it can be done by putting the cursor at the start of the line and selecting the position wanted. -- Don - Publisher 2000� Vancouver, USA "Max" <cmj@silkpurseproductions.comNOSPAM> wrote in message news:63084CB7-09E8-48FB-ACB8-2407F33DC0EC@microsoft.com... > Pub 07 W...

Format HTML Data As Text
We have a database file (linked by odbc) with a memo field that contains html data from e-mail messages. How can we reformat the data as text without the html tags? ...

Can't accept request for meeting
Using Outlook 2k on a Windows 2K machine user cannot accept a request for a meeting. Likewise they cannot make a request; Outlook says that the meeting doesn't exist even though this is a request to create a new meeting. This problem has to be within the .pst file. It was preexisting on previous machine and followed when the .pst file was moved to a brand new machine with Win2k and Off2k freshly installed. The .pst file is very large, around 800MB. ...

WHY CAN'T I SEND MY E-MAILS? THEY WIND UP UNSENT IN OUTBOX.
Why can't I send my e-mails? The wind up unsent in outlook. Version? do they change when you look in the outbox? http://www.slipstick.com/problems/outbox.htm -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Join OneNote Tips mailing list: http://www.onenote-tips.net/ "PatA" <PatA@discussions.microsoft.com&g...

How can I pre-filter a table that is used in a query in a live dat
How can I pre-filter a table that is used in a query in a live database? I have about 47 queries (SQL text in a memo field in a table) that all reference a few tables in the same database. I would like to limit the table’s data instead of re-writing all the queries to include further criteria. The queries are very complex and it is not desirable to re-write the queries and use parameters. This database is live on an ASP.NET web page and there may be multiple people viewing the reports which are using these queries. Since there are multiple users I can not simply copy the tables into ...

How to change cell shading/fill based on value in another cell?
I have a sheet with two columns of experimental data. Let's say the data is in F10:F25 (column 1) & G10:G25 (column 2). There is a value in another cell (A1) that contains a 1 or a 2. If A1=1, I want to turn shading in column 1 agree and in column 2 off. If A1=2, I want to reverse that. If there a worksheet function that will do that? I was hoping for something like: =cellshading(range,color) I could then execute one of these two statements: If A1=1 then cellshading(F10:F25,"Green") cellshading(G10:G25,"None") Else cellshading(G10:G25,"Green&...

Can't open Cash Receipts Entry
Hi After I updated from GP 8 to GP 9 I can't open the Cash Receipts Entry form, the system comes with this message "Cannot access this form because the directory containing it is not loaded". Is there a way to fix this and if there is how ? Best regards Andreas Høyer The user do not have access to the window. Go to Advance Security and give them access to the Cash Receipts entry window. Gerald "Andreas Hoyer" wrote: > Hi > > After I updated from GP 8 to GP 9 I can't open the Cash Receipts Entry form, > the system comes with this messa...

How do I randomly shuffle the cells in a column?
In Excel 2003, I have a column, say a1-a10, with certain entries. I need to create another column, b1-b10, with the same entries assigned randomly (shuffled) to the cells. Just one way .. Put in B1: =INDEX(A:A,RANK(C1,$C$1:$C$10)) Put in C1: =RAND() Select B1:C1, copy down to C10 B1:B10 returns a random shuffle of what's in A1:A10 Press F9 to re-shuffle -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Jason D" <Jason D@discussions.microsoft.com> wrote in message news:36244977-7EE5-436B-8617-59C15310B080@microsoft.com... >...

calculate null text field
Dear Helpers, I would like to creat a calculated field in a query that merch 3 text fields together, like, Index: [field A]&[field B]&[field C]. I don't want to replace the null field with anything so that if [field A] and [field B] are null and only [field C] contains data, the calculated [Index] field will look exactly like [field C]. Is it possible? I hope it is possible coz the Index field is a major field in my database. Thanks a lot to my helpers!! What happens if you type: [field A] & [field B] & [field C] into the Field row in query design, and choose A...

problem with ActiveSheet.QueryTables.Add for text file
I am using ActiveSheet.QueryTables.Add in Excel 2003 to import space-delimited text into a worksheet. The import works, but when I re-execute the macro with ActiveSheet.QueryTables.Add to import new data into the same sheet, all references to that sheet get shifted (i.e., what was a reference to cell B1 of the sheet with the imported data becomes a reference to cell C1. I would appreciate any suggestions. Thanks ...

Can I forward all messages received from a specific domain?
I'd like to set up a rule for an exchange user that forwards all mail they receive from a particular domain to another user's exchange mailbox. I tried setting up a rule where the "From" field was configured with a domain name only (From: specificdomain.com). I tested by having someone from that domain send an email to the user, but the rule did not fire; the message was never forwarded. Is there a valid syntax for creating a rule that should fire for all email from a particular domain? It's not practical in this case to set up multiple rules to fire on incom...

Outlook 2003 Font Changes When Replying Plain Text
Outlook 2003 is exhibiting a strange behavior when replying to plain text messages. After clicking the "Reply" button, the message appears in the Arial font. In about two to three minutes, the font suddenly changes to Courier which is the correct font. Why is it taking so long for the font to change? Steve Lockridge steve@websitewarehouse.com ...

How to get SUMPRODUCT on filtered cells
I'm currently using the formula =SUMPRODUCT((F2:I1475>=1)*(F2:I1475<=9999)) This gives me the number of the full range of cells but I need this to update whenever I use a variety of filters. Any help is greatly appreciated Sam.D Say we have data in A1 thru B29 like: flia value 0 pass 2 pass 1 pass 2 pass 1 pass 2 pass 0 pass 2 pass 1 pass 2 pass 1 pass 0 pass 2 pass 0 pass 1 pass 1 fail 1 fail 0 fail 2 fail 1 fail 0 fail 1 fail 2 fail 1 fail 1 fail 1 fail 1 fail 2 fail and are appling autofilter to column a. We want to use sumpr...

Sum Multiple Worksheets, same named cell
I have multiple worksheets containing the same named cell (e.g. Month_Total). On a totals worksheet, I am not able to use =SUM('START,END'!Month_Total) because of the local/global naming. Is there a way around this? (Month_Total will not always be in the same cell on each worksheet). Thanks! It seems to me that you have two options. 1. Name each of your "Monthly Total"s slightly different. OR 2. Do not use the cell range naming. "uncreative" <uncreative@discussions.microsoft.com> wrote in message news:60A150C6-50BC-4876-A31F-1C...

Can I export Distribution Lists?
A different department has asked us for a copy of all our distribution lists, members of those lists, their smtp e-mail addresses, x500 addresses, as well as group owners and who has rights to send to thos lists. How do I get that? We have about 100 distribution lists each with about 100 users. We are running Windows 2000 and Exchange 2000. Thanks Hi Boe, - Scripting this would produce much more readable output. I was able to modify a script I wrote a while back for general use. Posted to blog: http://www.suneja.com/blog/2006/02/script-list-distribution-groups.html It doesn't hav...

how can make a mail copy to a group
i using win 2003 , exchange 2003 if have a user mail need forward ( make a copy ) to many users how can i do this ? i just know how to make a copy to one copy thx On Sat, 12 Mar 2005 01:03:12 +0800, "tequila" <tequila529@gmail.com> wrote: >i using win 2003 , exchange 2003 >if have a user mail need forward ( make a copy ) to many users >how can i do this ? > >i just know how to make a copy to one copy >thx > > Have you looked at creating a Dirstribution Group within Active Directory Users & Computers? You can mail enable it and make a number of...

Test #46
Please ignore. Tatar <afe@ersoy.co.uk> wrote: > Please ignore. Wrong group. Post test messages in microsoft.public.test.here or microsoft.test -- Brian Tillman ...

Can I open an imbedded word doc from a cmd button?
I would like to imbed a word doc in my db (in a form?) and open it with a cmd button (like a help document). Is this possible and if it is how is this done? Many thanks, Joe M. On Wed, 18 Nov 2009 08:24:10 -0800, Joe M. wrote: > I would like to imbed a word doc in my db (in a form?) and open it with a cmd > button (like a help document). Is this possible and if it is how is this done? > > Many thanks, > Joe M. Why do you need to embed a document in your database? Store the document in a folder on your hard drive. Then open it from a command button whenever ...

can't print
why every time i'm using excel i can't print???? Because you dont have a printer? Seriously -- you need to be a tad more explicit in relating your problems Do you have a printer hooked up? Is it on? Have you verified that the printer is installed on your computer? Can you print from other programs? Do you have this printer selected as default printer in Excel? Do you get any error messages? If so, what do they say? "Artur Almeida" <arturalmeida@mail.com> wrote in message news:097b01c3b9a0$9aa1d970$a501280a@phx.gbl... : why every time i'm using excel i can'...

how can you change a "bitmap" to a jpeg??
After taking digital pictures in BITMAP, how can I change them to JPEGS? "TRM" <TRM@discussions.microsoft.com> wrote in message news:F5C091D7-377B-4F2F-A7BF-84794BC84F38@microsoft.com... > After taking digital pictures in BITMAP, how can I change them to > JPEGS? Get XnView or IrfanView. Both are free. Then use Save As and select the format. -- _________________________________________________________________ ******** Post replies to newsgroup - Share with others ******** Email: lh_811newsATyahooDOTcom and append "=NEWS=" to Subject. _________________...

Algebra within a cell
How do you set-up a formula in a cell that multiplies a constant times the number you insert? Ex. the constant is .315 remains present at all times only the number you insert changes - =.315*(x) You can't unless you use an event macro, if you need a formula you have to use another cell as help =0.315*A2 where A2 holds x you can also put 0.315 in a cell, copy it, select the cel with x and then paste special and select multiply. But to get this instantly you have to use something like Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Range("A2"), Target)...

How can you see the name of the person who has a shared document .
I deal with a lot of group folders and spreadsheets. If I try to open up a spreadsheet that is already openend by someone, a pop-up tells me that "abc123.xls is locked for editing by "xxx"." How can I get the person's name to show up instead of it being blank? Thank-you, Megan mac981, The name is the name the software is registered under (during installation). if that's not provided (left blank) during the installation, it'll show up blank. It's not too easy to change it.. you'll have to do that thru the registry. "missmac981" ...