Formula Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

livifivil - 29 Jul 2006 16:11 GMT

I am setting up a formula that can have a possible 3 results. I was
trying to use an "If, Then" statement but that will only cover two of
the results. I have a cell that has a drop down menu with four possible
coices: blank, "fixed", "rescheduled" and "pending." The problem I have
is that I used the formula:

=IF(I34="fixed", 3, 1)

What I want is to assign a point value to each choice.

Fixed = 3
Pending = 1
Rescheduled = 1
Blank = 0

The problem is that with the formula I have a blank cell will still
give one point. Any suggestions?

Signature

livifivil

Reply to this Message

Jay - 29 Jul 2006 16:37 GMT

You can nest up to seven IF functions so accomodating your 3 options is
straightforward. Try:

=IF(A1="Fixed",3,IF(OR(A1="Pending",A1="Rescheduled"),1,0))

Excel Help gives examples of Nested IF functions.

HTH

Jay

> I am setting up a formula that can have a possible 3 results. I was
> trying to use an "If, Then" statement but that will only cover two of
[quoted text clipped - 13 lines]
> The problem is that with the formula I have a blank cell will still
> give one point. Any suggestions?

Reply to this Message

livifivil - 29 Jul 2006 16:49 GMT

I tried that formula but it gives me 0 points for fixed and every other
value produces a "FALSE" error.

Signature

livifivil

Reply to this Message

Dave Peterson - 29 Jul 2006 17:05 GMT

Try the formula again--or type Fixed in A1 again. I suspect a typo with your
data entry in A1.

> I tried that formula but it gives me 0 points for fixed and every other
> value produces a "FALSE" error.
[quoted text clipped - 4 lines]
> livifivil's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36917
> View this thread: http://www.excelforum.com/showthread.php?threadid=566279

Signature

Dave Peterson

Reply to this Message

livifivil - 29 Jul 2006 17:28 GMT

I don't think I needed that "OR" in there. It works now. Thanks a lo
for your help

Reply to this Message

daddylonglegs - 29 Jul 2006 18:33 GMT

If those are your only options then why not

=IF(I34="fixed", 3, 1)*(I34<>""

Reply to this Message

Tom Ogilvy - 29 Jul 2006 22:09 GMT

> I have a cell that has a drop down menu with four possible
>choices: blank, "fixed", "rescheduled" and "pending."

Signature

Regards,
Tom Ogilvy

> If those are your only options then why not
>
> =IF(I34="fixed", 3, 1)*(I34<>"")

Reply to this Message

Dave Peterson - 29 Jul 2006 22:39 GMT

another variation:
=(1+2*(I34="fixed"))*(I34<>"")

> If those are your only options then why not
>
[quoted text clipped - 5 lines]
> daddylonglegs's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30486
> View this thread: http://www.excelforum.com/showthread.php?threadid=566279

Signature

Dave Peterson

Reply to this Message

Jay - 30 Jul 2006 12:30 GMT

> I don't think I needed that "OR" in there. It works now. Thanks a lot
> for your help.

I thought I'd put the OR in to show you an additional tool which you may
find useful in the future. Your original post implied that you weren't
aware of nested IFs so my answer tried to incorporate this. The OR is
an additional tool which can limit the number of nests required.

Jay

Reply to this Message