We help IT Professionals succeed at work.

Improve ratio

correlate
correlate used Ask the Experts™
on
Dear Experts,

I have a spreadsheet which calculates a win / loss percentage (attached) for tennis rankings.

To improve my ranking I need to achieve the following in a qualifying period: 6 wins and a win loss ratio of 60:40.  

This bit I can calculate & show all OK, but what I would like to be able to show in the Column G of the sheet "RESULTS" is how many successive wins I need to make to achieve the required ratio.  (i.e. if I have played 12 matches, won 6 & lost 6 I have achieved the 6 wins but I fail on the ratio, so in order to achieve the ratio how many more wins do I need to make without loss)

Book1.xlsx

Can Anybody help?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rob HensonFinance Analyst

Commented:
6 out of 12 gives win ratio of 50%

To improve up to 60% you have to improve by a relative 20% (10%/50%).

20% of 12 games is 2.4 so therefore need to win 2.4 more games. Obviously can't win part of a game so have to win 3.

As you increase the number of games played the % will obviously change which is presumably why 9 out of 15 gives the required 60%.

Some more examples would be good. Also, do you need to include the option when below 6 wins; number of games required to have 6 wins AND 60%?

See attached as a starter.

Thanks
Rob H
Win-ratios.xls

Author

Commented:
Hi Rob H,

thats brilliant - thank you, ideally yes I would like to include the option when below 6 wins; number of games required to have 6 wins AND 60%, would this be an if statement, ie if Wins is greater than 6 then (your formula) - =ROUNDUP((A3*(F2/E3))-A3,0)

if its not then its ???
Finance Analyst
Commented:
Slightly improved version of the file attached but I am not convinced its still completely correct.

I have added a column for number of wins required to bring up to 6 and then number of wins to get to 60% but both are based on current situation and not ongoing ie if player gets to 6 wins they might then be at the 60% target without having to get the additional games originally forecast as required to get to 60% target. Not sure this would be possible and can't get my mind round the logic at the minute.

I guess another way to look at it would be to consider the loss ratio being only 40%.

Going to be tied up with a few work things for next few hours but will get back onto it later.

Thanks
Rob H
Win-ratios.xls

Author

Commented:
I added in 3 further columns & that cracked it which, 2 with an if statement removed negative numbers from the required number & the required ratio and then a sum to add up the results of those 2, then to remove any circular references made the column "Played" a sum of Wins & losses - the result is attached - thank you very much for your help
Win-ratios-v3.xls

Author

Commented:
Brilliant - thank you very much for your help