+ Reply to Thread
Results 1 to 11 of 11

Formula not calculating correctly

  1. #1
    Registered User
    Join Date
    06-29-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    5

    Formula not calculating correctly

    Hi guys, I'm new to forums and although I've been using excel for about 15yrs, I'm pretty new to excel formulas. I have this problem where I am trying to create a handicap progression log and as far as I can tell, the formula is correct but the answers are coming out incorrect. So basically on 'Sheet 1' I have a scorecard from that week as in the pic below;

    excel problem 1.jpg

    On 'Sheet 2' I have made the handicap log which shows their average scratch score then their handicap as you can see in the pic below;

    excel problem 2.jpg

    So to work out Chris Mckimm's average scratch score (in cell B11) I have used the following equation;

    =AVERAGE(Sheet1!C6,Sheet1!E6,Sheet1!G6)

    Which has come out at 121.3 which is correct. To then work out the handicap it's the 80% of the difference between your average scratch score and 180 so if I average say 140, 80% of the difference would be 32. So I used the following equation (for cell B12);

    =SUM(180-B11)*(80%)

    I apologise as the picture I've posted only shows it to 1 decimal place but if I set it up to show 2 decimal places it comes out at 46.93 yet when I do this on a calculator it comes out as 46.96. I also tried the following equation;

    =SUM(180-B11)*0.8

    That still gives the same incorrect answer. Any ideas why or what I'm doing wrong?

    - Thanks in advance, Adam

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,230

    Re: Formula not calculating correctly

    Adam

    From what you describe it sounds like it's possibly a rounding problem.

    Hard to tell though just from images.

    Can you upload an example workbook?

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

    By the way, when I do the calculation with a calculator I get the result 46.933....
    If posting code please use code tags, see here.

  3. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: Formula not calculating correctly

    You're much more likely to get useful assistance if you post a workbook rather than a picture. Have a look at the forum rules.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    06-29-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Formula not calculating correctly

    Apologies, I wasn't aware I could attach a workbook. Thanks for the quick replies guys and I've just double checked the calculation on my phones calculator in case my pc one was playing up and still getting 49.96. The workbook is attached below

    Bowling League Scorekeeper - Weeks 1 to 4.xlsx

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,107

    Re: Formula not calculating correctly

    In fact, the answer is not 121.3, it is 121.3 recurring, that is 121.333333333....

    If you want to see a "correct" answer to 1 decimal place, you will need to use ROUND.


    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    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


  6. #6
    Registered User
    Join Date
    06-29-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Formula not calculating correctly

    Ah right, so it's not giving the incorrect answer to the handicap, it's not showing the full answer to the scratch average but when it's doing it's equation it's working it out off the full 121.3333333333333333... I see.

    So is there any way I can force it to round up or down? I mean actually force it to round a certain way? I'm not sure how they are working it at the moment but I'm guessing that your handicap would always be rounded down so for example Steve Bridger's handicap of 51.7 I think they would only allow a handicap of 51 until he actually reaches the 52 (I'll know this for definite tomorrow anyway)

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: Formula not calculating correctly

    Please Login or Register  to view this content.
    =INT(your formula) removes the decimals

  8. #8
    Registered User
    Join Date
    06-29-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Formula not calculating correctly

    Quote Originally Posted by shg View Post
    Please Login or Register  to view this content.
    =INT(your formula) removes the decimals
    Will that only round it down or will that round it to the nearest? Thanks for all the quick replies guys

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,107

    Re: Formula not calculating correctly

    It takes the integer part of the value. So, effectively, it always rounds down.

    Regards, TMS

  10. #10
    Registered User
    Join Date
    06-29-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Formula not calculating correctly

    Marvellous, thanks once again to all

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,107

    Re: Formula not calculating correctly

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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