+ Reply to Thread
Results 1 to 13 of 13

Unwanted Rounding

  1. 10-30-2010, 12:19 AM #1
    IntegraCraig
    IntegraCraig is offline
    Registered User
    Join Date
    10-30-2010
    Location
    St. Catharines, ON
    MS-Off Ver
    Excel 2003
    Posts
    6

    Unwanted Rounding

    For school, I have to create a casino game and calculate all the probabilities and expected payouts, etc. I am trying a simple calculation of expected payout:

    =600*0.5*V5-600*(1-V5)

    V5 being equal to the probability of winning under that circumstance. For example, the calculations should be equal to:

    =600*0.5*V5-600*(1-V5)
    =300*0.2778-600*(1-0.2778)
    =83.34-433.32
    =-349.98

    However excel will round up to $-350.00 (with 2 decimals displayed). This roundoff error is putting my total expected value off by 4.77. Can this be fixed?
    Reply With QuoteRegister To Reply
  2. 10-30-2010, 12:38 AM #2
    Paul
    Paul is offline
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Unwanted Rounding

    Hi Craig, welcome to the forum.

    How exactly is Excel rounding up to -350? Formatted to two decimal places, my cell shows exactly -349.98.

    Are you using a ROUND function somewhere else?
    - Paul

    Click here to read the Forum Rules

    Reply With QuoteRegister To Reply
  3. 10-30-2010, 01:07 AM #3
    IntegraCraig
    IntegraCraig is offline
    Registered User
    Join Date
    10-30-2010
    Location
    St. Catharines, ON
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Unwanted Rounding

    Thanks for the welcome Paul.

    I haven't used anything to round the numbers except cell formatting. My game involves drawing cards and attempting to roll the value of the card using the sum of two dice.

    Theres a series of complex calculations used to derive the probability of winning when 2 and 3 cards are drawn. I've used VLOOKUP to reference the probabilities from a source table of hitting the individual cards.

    I have a feeling the VLOOKUP formulas are causing this problem, since typing the equation mentioned in the first post with no cell references will produce the right result. Any additional insight?
    Reply With QuoteRegister To Reply
  4. 10-30-2010, 01:12 AM #4
    Paul
    Paul is offline
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Unwanted Rounding

    Not really. Can you post a copy of your workbook and show us where it's showing incorrect data?

    Cell formatting doesn't actually affect calculations on that cell UNLESS you have Excel calculation set to "Precision as displayed" (in 2003 I believe it's in Tools -> Options -> Calculation tab?).

    Information about "Precision as displayed": http://support.microsoft.com/kb/214118
    Reply With QuoteRegister To Reply
  5. 10-30-2010, 01:33 AM #5
    IntegraCraig
    IntegraCraig is offline
    Registered User
    Join Date
    10-30-2010
    Location
    St. Catharines, ON
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Unwanted Rounding

    Hopefully the file attached properly...

    The discrepancy occurs in columns P and X at the bottom of the tables. Cell P174 and X2202 run validations against cell O176 and W2204 and do not equate. Column X is the most peculiar since the percentages are all repeating decimals yet the result is a nice tidy pattern of even numbers incremented in 25.

    The precision as displayed setting was unchecked as well as the Fixed Decimals setting under the Edit tab.
    Attached Files Attached Files
    Last edited by IntegraCraig; 10-30-2010 at 01:37 AM.
    Reply With QuoteRegister To Reply
  6. 10-30-2010, 05:23 AM #6
    teylyn
    teylyn is offline
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Unwanted Rounding

    P174 is =AVERAGE(P5:P173) and calculates correctly, as far as I can see.
    X2202 is =AVERAGE(X5:X2201) ans calculates correctly, as far as I can see.

    Cell P174 and X2202 run validations against cell O176 and W2204
    No, they don't. They contain an Average formula.

    The attached spreadsheet clearly does not match the narrative. Please attach a file that features the issues you are experiencing.
    Last edited by teylyn; 10-30-2010 at 05:27 AM.
    Microsoft MVP
    http://www.teylyn.com
    Twitter: http://twitter.com/IngeborgNZ
    Reply With QuoteRegister To Reply
  7. 10-30-2010, 10:06 AM #7
    IntegraCraig
    IntegraCraig is offline
    Registered User
    Join Date
    10-30-2010
    Location
    St. Catharines, ON
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Unwanted Rounding

    Quote Originally Posted by teylyn View Post
    P174 is =AVERAGE(P5:P173) and calculates correctly, as far as I can see.
    X2202 is =AVERAGE(X5:X2201) ans calculates correctly, as far as I can see.



    No, they don't. They contain an Average formula.

    The attached spreadsheet clearly does not match the narrative. Please attach a file that features the issues you are experiencing.
    P174 and X2202 do contain average formulas, but the answer if done correctly should be equal to the values in O176 and W2204. That's what I meant by validation.
    Reply With QuoteRegister To Reply
  8. 10-30-2010, 01:21 PM #8
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Unwanted Rounding

    Are these the rules?
    The player plunks down his bet -- say, $100.

    He draws one card, then rolls two dice. If the sum of the dice match the card, he gets his bet back plus $200.

    If not, he draws a second card and rolls the dice. If the sum matches his second draw (or either his first or second draw??), he gets his bet back plus $100.

    If not, he draws a third card and rolls the dice. If the sum matches his third draw (or any of his three??), he gets his bet back plus $50.

    If not, he forfeits his bet.
    Also, how many time can the player play before the cards are shuffled?
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  9. 10-30-2010, 01:41 PM #9
    IntegraCraig
    IntegraCraig is offline
    Registered User
    Join Date
    10-30-2010
    Location
    St. Catharines, ON
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Unwanted Rounding

    You've got the concept right. Player bets $10 (for example) and sees one card and attempts to hit the value of the card with the dice, winning 2x his bet. If he fails, he has the option to double his bet to see the second card and hit the value of either two cards, winning even money on the total $30 bet. If he fails again, he has the option to triple his initial bet to see a third card and roll the sum of any of the 3 cards. If he wins, he gets 0.5x his total $60 bet. If he fails, he loses the entire $60

    I have the probabilities calculated, but for additional marks, I'm attempting to create a 'perfect game strategy'. Similar to blackjack, this strategy will maximize a players expected payout. However the expected payouts for each combination of hands (columns P and X) are being rounded unwantingly. The roundoff error is too significant to ignore. Comparing cell X2202 and W2204, the difference is $4.77 and I need to eliminate the discrepancy.
    Reply With QuoteRegister To Reply
  10. 10-30-2010, 02:06 PM #10
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Unwanted Rounding

    If the initial bet is $10 and he doubles it, it's $20, is it not? And doubling again is $40 total, right?
    Reply With QuoteRegister To Reply
  11. 10-30-2010, 02:14 PM #11
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,445

    Re: Unwanted Rounding

    Sounds like he pays $10 for the first card, another $20 for the second and another $30 for the third.

    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  12. 10-30-2010, 02:42 PM #12
    IntegraCraig
    IntegraCraig is offline
    Registered User
    Join Date
    10-30-2010
    Location
    St. Catharines, ON
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Unwanted Rounding

    Quote Originally Posted by TMShucks View Post
    Sounds like he pays $10 for the first card, another $20 for the second and another $30 for the third.

    Regards
    Thats correct.
    Reply With QuoteRegister To Reply
  13. 10-30-2010, 03:35 PM #13
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Unwanted Rounding

    I'm not at all sure, but think the attached analysis is correct.

    I'd do a Monte Carlo simulation with 10,000 or so trials to see if it's correct.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1