Wednesday, July 14, 2010

Evaluating Text with an IF() Statement Using Microsoft Excel

All:

Earlier today I got a question from a classmate:

Doug,

How would you program your IF statement in excel based from Figure 13.60 from our textbook? I know how to do it if it is just a number, but the Q1-XX format is throwing me off and I can't make my table look like the one in the figure.
Here is the answer:

In order to evaluate some of a text string, you have to use one of three functions.
=LEFT()
=MID()
=RIGHT()

In this case we will use =LEFT().

The =LEFT() function has two arguments:

=LEFT(text,number of characters)

So, point to the cell (text) that you want to evalate and return the number of characters starting at the LEFT.
If Cell A2 has Q1-92 and you type in =LEFT(A2,2), the formula will return "Q1", because it returns the first two characters starting at the left of the text string.

The same holds true for =RIGHT(), it just works the other way (from the right, oddly enough).

=MID() works a bit differently.

=MID(text,start number, number of characters).

In this function, you point to the text, then tell it at what character you want to start evaluating and then the numer of characters.

So, lets say you want the decade from the text.

=MID(A2,4,1). This will return "9". It looks at the text string, counts over 4 from the left and returns 1 character.

So, how does this help me with the IF statement? I hear you asking.

You combine the IF() and the LEFT().

It looks like this:
=IF(LEFT(A2,2)="Q1",1,0)

It reads like this ... If the first two characters in cell A2 are equal to "Q1", then return a 1, otherwise return a 0.

Hope that helps everyone!

2 comments:

Anonymous said...

You Show-off...

Greg Huber

Hybrid said...

Hey. You have a great blog. Thanks for sharing.

Follow me on Twitter @ http://twitter.com/thinkhybrid or check out my blog @ http://hybridnow.blogspot.com/