# Formula not calculating correctly

1. ## 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?

2. ## Re: Formula not calculating correctly

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....

3. ## 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.

4. ## 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. ## 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

6. ## 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. ## Re: Formula not calculating correctly

``Please Login or Register  to view this content.``

8. ## Re: Formula not calculating correctly

Originally Posted by shg
``Please Login or Register  to view this content.``
Will that only round it down or will that round it to the nearest? Thanks for all the quick replies guys

9. ## Re: Formula not calculating correctly

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

Regards, TMS

10. ## Re: Formula not calculating correctly

Marvellous, thanks once again to all

11. ## Re: Formula not calculating correctly

You're welcome.

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

#### 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