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

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

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)

Originally Posted by shg

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

Marvellous, thanks once again to all