Results 1 to 2 of 2

Hoping to find equation to solving conflicting results from 1 data point.

Threaded View

  1. #1
    Registered User
    Join Date
    01-22-2017
    Location
    Wilmington, NC
    MS-Off Ver
    Excel for Mac Ver. 15.21.1
    Posts
    1

    Hoping to find equation to solving conflicting results from 1 data point.

    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!
    Attached Files Attached Files
    Last edited by Parkerarob; 01-23-2017 at 10:46 AM. Reason: Added sheet

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Antoine Equation Solving for Boiling Point writing a UDF
    By eac65 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-07-2013, 03:47 AM
  2. Conflicting formatting results
    By jacob@thepenpoint in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 01-25-2012, 04:52 PM
  3. Solving an equation
    By sydmil in forum Excel General
    Replies: 6
    Last Post: 05-13-2009, 12:29 AM
  4. Replies: 6
    Last Post: 12-09-2007, 11:09 AM
  5. [SOLVED] Please help me solving this equation ...: )
    By Joe Satriani in forum Excel General
    Replies: 3
    Last Post: 02-22-2006, 01:50 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1