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

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?

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





livifivil wrote:
> 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?
>
>

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

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



livifivil wrote:
>
> I tried that formula but it gives me 0 points for fixed and every other
> value produces a "FALSE" error.
>
> --
> livifivil
> ------------------------------------------------------------------------
> livifivil's Profile: http://www.excelforum.com/member.php...o&userid=36917
> View this thread: http://www.excelforum.com/showthread...hreadid=566279

--

Dave Peterson

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

If those are your only options then why not

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

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

--
Regards,
Tom Ogilvy

"daddylonglegs" <[email protected]>
wrote in message
news:[email protected]...
>
> If those are your only options then why not
>
> =IF(I34="fixed", 3, 1)*(I34<>"")
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile:
> http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=566279
>

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

daddylonglegs wrote:
>
> If those are your only options then why not
>
> =IF(I34="fixed", 3, 1)*(I34<>"")
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=566279

--

Dave Peterson

livifivil wrote:
> 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