If statemant with 3 out comes?

[aiden07]

I don't know if this is possible or not so I really need some advice please.

All I need to know is, if there is an 'IF' formula that can be used in Excel that gives you 3 out comes.

I know that with a standard 'IF' formula you have just 2(true and false)like this:
=if(A5>100,"Pass","Fail")

But is it possible to have 3 out comes?

Say for example there is a competition being recorded on excel and the scores determine whether or not the out come is "WIN", "LOSE" or "DRAW".

If a person wins then they get say 3 points. If they lose they get 0 points and if they draw they get 2 points.

How could you do this? I would really appreciate any ideas and advice.

Thanks

 

[westma]

Hi,
Try something like
=IF(A5<>100,IF(A5>100,"Pass","Fail"),"Draw")

Nested IF's work well to obtain multiple outcomes.

 

[macropod]

Hi aiden07,

You can do this without an IF test! For a result that delivers 3,2 or 0 points in A1, you could use the following formula:
=A1-2
combined with a custom number format for the cell:
"Win";"Lose";"Draw"

Basically, all you need to do for this to work is to apply a formula that reduces the 'score' to any positive value for a win, 0 for a draw and any negative value for a loss. The custom number formatting does the rest.

Cheers

[MS MVP - Word]

 

[aiden07]

Thanks people, i will give them a go!

Any other suggestions are welcome too.

 

[yogia]

Hi aiden07:

You can modify the IF formulation as ...

=IF(A5=3,"win",IF(A5=2,"draw",IF(A5=0,"lose","")))

and here are couple of other formulations that you find to be of interest ...

=LOOKUP(A5,{0,"lose";1,"";2,"draw";3,"win"})
and
=CHOOSE(A5+1,"lose","","draw","win")

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[aiden07]

Hey, I have been trying some of your ideas but I'm not sure they are quite what I am looking for. Perhaps I haven't explained my problem correctly so here is an example of what I am trying to achieve.

 

[xlhelp]

There is a lot more work that needs to be done. What you have looks very simplistic. What I assume is that you want cumulative totals as time progresses. So, not only you need to find out points for the week, you need to figure out how you are going to assimilate the info. Some kind person in this forum may help you do your whole project for you. (Sadly, I am not that kind). I believe the basic seed for 3 conditions has been provided to you.

If something does not work the way you expect it to, then, please post and someone will gladly help.

Member- AAAA Association Against Acronym Abusers

 

[yogia]

Hi aiden07:

Thanks for posting the worksheet so we can see how exactly is your data laid out. Having said that, your layourt of the data is not well suited for working with EXCEL List/Database. I have also assumed that you could be adding more players.

In any event, working with your layout as you have posted, I have used two helper columns H and I so that the formulas do not get more unwieldy and convoluted.

Formula in cell H4 is ...

=ISNUMBER(MATCH($F4,$A:$A,0))+ISNUMBER(MATCH($F4,$C:$C,0))*3

Formula in cell I4 is ...

=MATCH($F4,INDEX($A:$C,0,$H4),0)+1

Formula in cell G4 is ...

=CHOOSE(SIGN(INDEX($A:$C,$I4,$H4)-INDEX($A:$C,$I4,IF($H4=1,3,IF($H4=3,1))))+2,0,2,3)

the formulas in G4,H4,and I4 are then copied down.

Let me know how this works out for you.






Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[Hasit]

aiden, to follow on from xlhelp - your situation is more complex than first described. Happy to help, but need more information about how the data set will change over time, what the frequency is etc.

If it is as xlhelp describes it, then thats a lot more complex than originally understood.

 

[aiden07]

Hello everyone,

Thank you so much for all of your efforts with this. I am going to give some of the ideas that I have been given to test and see if I can get the result I require.

Thanks again!

Aiden.

 

[macropod]

Hi,

Your worksheet image shows you're after something quite different from what you originally asked.

Here's a formula you can enter into G4 and copy down to G11. It returns 3, 2, or 0 for each player, for a win, draw or loss, respectively.

=IF(IF(ISERROR(MATCH(F4,A$3:A$12,0)),OFFSET(C$3,MATCH(F4,C$3:C$12,0),),OFFSET(A$3,MATCH(F4,A$3:A$12,0),))-IF(ISERROR(MATCH(F4,A$3:A$12,0)),OFFSET(A$3,MATCH(F4,C$3:C$12,0),),OFFSET(C$3,MATCH(F4,A$3:A$12,0),))>0,3,IF(IF(ISERROR(MATCH(F4,A$3:A$12,0)),OFFSET(C$3,MATCH(F4,C$3:C$12,0),),OFFSET(A$3,MATCH(F4,A$3:A$12,0),))-IF(ISERROR(MATCH(F4,A$3:A$12,0)),OFFSET(A$3,MATCH(F4,C$3:C$12,0),),OFFSET(C$3,MATCH(F4,A$3:A$12,0),))<0,0,2))

Cheers

[MS MVP - Word]

 

[aiden07]

Hey,

thanks again but I think you guys should stop trying now. You are going through so much trouble to do this for me!! And i really do appreciate it. But it appears it is way more complicated than I could have imagined! This spread sheet I have given you is only an EXAMPLE of what I want. The Original one is much bigger and set out slightly different. So even if you gave me a way of doing it on this example one. I would still need to edit it in order to get it to work on the original one, and I am afraid that is way beyond anything I am capable of.
So really, Thanks again for all your efforts but I might end up just doing it the 'old fashioned way' of typing in the numbers in manually.

cheers!

Aiden.