Issue:
I want Excel to determine two amounts (win and loss) from a given set of data correctly:
Total Points (given data point) where 173 point equals a win and 17 points equal a loss.
A1 represents the Total Points
My current win formula(B1) is =ROUNDDOWN((A1)/173)
My current loss formula(C1) is =SUM((A1-(173*B1))/17)
This works fine until it tries to calculate more than ten losses (11 losses = 187 points).
I found a way to work around this by using a modifier formula. I changed the Win formula to
=(QUOTIENT(A1,173)-(N1) where N is the extremely long modifier which is asking the question if the Win column is not a whole number then subtract 1,2,3, etc. until a whole number is found.
=IF((MOD((A1-(QUOTIENT(A1,173)*173)),17=0,0,IF((MOD((A1-((QUOTIENT(A1,173)-1)*173)),17)=0),1,IF((MOD((A1-((QUOTIENT(A1,173)-2)*173)),17)=0),2.....and so on until 25.
My final problem is that once 17 losses are reached the formula will not work correctly because 17 Wins and 173 Losses equal the same amount of points - 2941.
Is there a way to make Excel correctly calculate more efficiently than I have above and is there a way for it to calculate correctly past 17 Losses.
Thanks for any help!
Bookmarks