Calculating Win/Loss, knowing total points, number of matches, and point values.

Hi and welcome to the forum

Try this...

="this = "&INT(B2/3)&" Wins and "&MOD(B2,3)&" Losses"

You can remove the text if you want and just use...
=INT(B2/3) for Wins
=MOD(B2,3) for Losses

="this = "&IF(B2=A2,A2&" Losses",INT(B2/3)&" Wins and "&MOD(B2,3)&" Losses")

Originally Posted by humdingaling

doesnt work with 13 points
done by 1 win - 10 loses


previous formula gave 4 wins 1 lose (correct points but not enough matches)

11 games 10 points could also be 4 wins and 1 loss

Using simple math, you can figure this out easily:
Let's say number of wins is a, number of loses is b, then we have
a + b = number of matches
3a + b = total score

Then a = (total score - no. matches) / 2
and b = number of matches - a
Assuming your total score is in Cell A2, and number of matches in Cell B2 (A1 and B1 are headers), then
number of wins = ROUNDDOWN((A2-B2)/2,0)
number of loses = B2 - C2

In case this gives wrong results, say total match = 11 but number of matches = 6, the formula will give 2 as no. of win, and 4 as no. loses.
Think of it, can you do a better job? 2 and 4 and 10 points is the closest possible, because with 6 matches there's no way you can achieve 11 points:
1 win 5 loses, 3+5 = 8 points
2 wins 4 loses, 6+4 = 10 points
3 wins 3 loses, 9+3 = 12 points
That's why I put a ROUNDDOWN there to avoid having 2.5 wins

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.