Football: Excel formula needed

Andybtsn · 27 Mar 2007 at 09:06

Hey,

I play football with work colleagues on Monday nights and am trying to setup a spreadsheet for a ratio of win for each players.

At the moment, I just have:

Player name - Appearences - Wins - Losses - Win ratio (%)

Some players have played 6 games, some have played 1 game, so obviously the person who has 1 game and 1 win will always be top via the win ratio. Is there a way of bringing appearences into the formula?

Cheers

Andy

Girdag · 27 Mar 2007 at 09:08

Maybe only count people with more than 5 (or whatever number) games?

That shouldn't take long, just use an IF function to seperate them out.

moid · 27 Mar 2007 at 13:14

Or just use point scoring (like in real life). 3 for a win, 1 for a draw etc

Solves the whole % issue.

Fr0dders · 27 Mar 2007 at 13:30

lol this is easier than it sounds

its just wins / divided by appearances x 100.

You dont need to factor in the losses. After all, if they turned up, and didnt win, they must have either drawn or lost

so formula will be

=(wins\appearances)*100

just change the wins and appearances bit for the cell that value is in. Then select your formula, and drag the selection box down to drop this formula into everybody elses below

Girdag · 27 Mar 2007 at 13:45

MrLOL said:
lol this is easier than it sounds

its just wins / divided by appearances x 100.

You dont need to factor in the losses. After all, if they turned up, and didnt win, they must have either drawn or lost

so formula will be

=(wins\appearances)*100

just change the wins and appearances bit for the cell that value is in. Then select your formula, and drag the selection box down to drop this formula into everybody elses below

Re-read his post, he already has that bit.

slinxy · 27 Mar 2007 at 16:30

add a second sheet that grabs the info of the first sheet and sort it order of win percentage. So the first sheet will be raw data and the second will be the results.

Fr0dders · 27 Mar 2007 at 16:50

Girdag said:
Re-read his post, he already has that bit.

just having re-read his post. he's asking for the wrong thing. he asked for a formula that weighted it by appearnaces

i've just provided one, but it doesnt change the fact the person who has played 1, won 1 will allways appear top on "win ratio"

what he needs is to sort his people out by something other than win ratio. Win ratio by definition, is allways going to put the person who's played 1, won 1 first

unless you want to rank it by something else, say some arbitrary score. and have a score which is a multiplier of the win ratio by total number of wins. But that way the person who has played 4, won 2 will score 100 %. (2 / 4 = 0.05 * 100 = 50% x 2 = 100 %). The same as somebody who has played 1 won 1. But somebody who has played 4 won 4, will come much higher up the league at 400 %

so maybe take the above formula then add on * total wins

so 1 coloum for Win Ratio. Then another colum for Ranking and have that as =winratiocolum*totalwinscolum

maybe ?