Help needed to generate a “best of 3” formula

Here’s the situation: If a player wins 2 out of 3 matches s/he progresses to the next round in the main tournament and if a player losses 2 out of 3 matches s/he goes to the consolation tournament.

The wins and losses are typed into the excel spreadsheet as W or L.

How do I get excel to calculate that 2 out of 3 Ws equals an overall win and 2 out of 3 Ls equal an overall loss and then copy the winners name (which is on another cell) to the next slot and the losers name (also in another cell) to the consolation tournament slot?

I’d also like to know how to automatically grey out the cell of the losing player.

To make that all a little bit easier to understand please view this URL:
http://www.backpacker-backgammon.com...t_of_three.xls

Thanx in advance.

Mike

Hi Mike,

Sorry for such a stupid question, but i do not know the game ...
is 2 out of 3 a ratio ? or is it that a match is always made up of 5 games ?

Carim

Hi Carim,

Usually in a backgammon match it is the first player to a certain number of points (say 7) that wins the match.

However for this event it will be the best of 3 (7 point) matches.

I know how to program a single win / loss but this "best of 3" idea has me stumped.

Thanx,

Mike

Hi,

Do you plan to input 1,2,3 or W, L ... ?

Carim

W and L

These can be altered to whatever is required to make it work further down the excel spreadsheet out of view.

What do you think of this first attempt ...?

HTH
Carim

Thanx for your attempt but names are not carrying through when I enter a 3rd result.

Are you running macros? I have no idea about marcos and got a warning message about them being present.

Sorry but you do have to accept macros ...

and inputing a W or a L in cell E6 does trigger what you requested ...

HTH
Carim

Thanx for your help on this Carim – ok – I’m not too sure about macros but I now have cell F12 working of if I put a W in cell E6.

But this isn’t quite right. Because it is a “best of 3” Player A could win matches one and two (cells C6 and D6) and thus qualify for the next round without playing match 3 (recordable in cell E6.) Or Player A could lose his first two matches and thus Player B would qualify.

Is there anyway this could be achieved without using macros to save me learning a whole new area of excel I have no idea about? (I’m struggling with formulas alone!)

Hi again,

Have a go with the following attempt ...

HTH
Carim

Ok – we’re getting there but not still quite there yet.

The function still isn’t perfect. If I type in L L in C6 and D6 the right hand side columns no longer gives values and Player B’s name does not carry though to Main Tournament 2.

If I accidentally type in the wrong result (very easy to do) I cannot go back and correct it – that could cause huge problems.

This clean button is useful now but highly dangerous on the end product because it could wipe the entire tournament at the touch of a button.

Agg, now we got visual basic editor in there as well – another application I know nothing about. I don’t wish to sound ungracious but I’m no computer programs expert – can we keep this all simple and just use excel formulas and conditional formatting?

Hope you’re still willing to help,

Mike

Hi again,

HTH
Carim

Ok – it’s all looking very good now, thank you for this much, but where have all the formulas gone? Without the formulas I don’t know how to copy whatever coding you are using to other cells on the full chart.

Further I’m not keen to use a system that I don’t understand. So if we are going away from using formulas I need to learn a little bit more about how you are achieving what you are achieving. Please. Thanx.

Mike

Hi again,

Should be Ok now ... with the "undo" feature ...

HTH
Carim

Glad your problem is fixed ...