I have MS Excel 2010 running in Windows 7

There are 19 columns of data

Cell A1 is where I enter digits 1 to 19 (depending of requirements)

I would like to have a formula that says:If A1 is between 1 and 7, go to cell B2

If A1 is between 8 and 14, go to cell C2

If A1 is between 15 and 19, go to cell D2Thank you.

When posting in a Help forum such as this, please use a subject line that gives us some indication about what your post is about. If everyone used a generic subject line such as "Excel Equation Help" we wouldn't be able to tell one post from another.

I have edited the subject line of this thread.

DerbyDad03

Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

What to you mean when you say " go to cell B2"?A formula can't select a cell or move the cursor.

You could probably do it with VBA (a macro) but it would help if you clarified your request first.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thank you for your help and input. Sorry I wasn't clear. This is the formula that I did:

=IF(A1<=7,B2,IF(A1<>8-14,C2,IF(A1>=15,D2)))

When i enter 1 in cell A1, I get good result (from cell B2). So is 2 thru 7, and good results from C2 for 8 thru 14..

When I enter 15 (or 16 thru 19) I get FALSE.

Are you sure that you posted the formula that you used in your spreadsheet? Using the formula you posted (copied directly from your post) I do not get FALSE for any number, nor would I expect to.

An IF statement is evaluated from left to right, and stopping when a TRUE is returned.

Let's break down the formula that you posted:

=IF(A1<=7,B2,IF(A1<>8-14,C2,IF(A1>=15,D2)))

IF(A1<=7,B2,... TRUE for any value less than or equal to 7.

IF(A1<>8-14,C2... TRUE for any value not equal to 8 minus 14 (-6)

IF(A1>=15,D2... TRUE for any value greater than or equal to 15, but it will

nevermake it that far based on the inputs you are using.When A1 contains any number from 7 and below, the first condition will be TRUE and the value in B2 will be returned.

When A1 contains

anynumber above 7, it won't be equal to -6 and the value from C2 will be returned.If you are getting FALSE with values from 15 - 19 in A1, you must be using something different than what you posted.

All that said, this is the formula that should work for you, remembering that the evaluation will stop as soon as a condition is TRUE:

=IF(A1<=7,B2,IF(A1<=14,C2,D2))

Note: This formula will return the value in B2 for any number from 7 down to negative infinity and the value in D2 for any number from 15 up to positive infinity.

Since you say you will only be entering 1 - 19, that shouldn't be a problem. If you need to return something different if the value in A1 is something other than 1 - 19, let us know.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you think Amazon can re-invent the convenience store?

Discuss in The Lounge

Poll History