Slightly incorrect formula results

The problem is you have the cells formatted to only 2 decimal places.

The average of the first column is NOT 123.54. It is actually 123.53833333
But you have the cell formatted to only show 2 decimal places, so it 'Appears' to be rounded UP to 123.54.
When in fact, that is only being DISPLAYED that way, but the actual complete value with 4+ decimals still exists in the cell.
So when you're doing the *52/12, you're doing that on 123.5383333..NOT 123.54


Hope that helps.

Oh ok, I see what it was doing. I tried method 2 - ticking the "Precision as Displayed" option - and that seems to work fine. The warning "data will permanently lose accuracy" was a bit ominous though!

As it happens, I will be integrating my spreadsheet into another spreadsheet (which also works with currency to two decimal places), and I had noticed that the rounding was occasionally a bit odd in that one too. So activating that option in the other spreadsheet should also fix that.

Thanks for the help everyone!

I would recommend using the ROUND function instead of the precision as displayed.
Because of the ominous warning that it's permanent AND it applies to ALL Numbers..

So just round the result of your average..

=ROUND(AVERAGE(....),2)

Well if the whole spreadsheet (and the spreadsheet it is to be integrated into) only works with currency to two decimal places anyway, then it should be ok shouldn't it? I'd rather not have to add the ROUND function to every formula on my two tabs, and then also to all of the formulas in the tabs of the other spreadsheet too...

I guess it depends on which answer you believe is correct.

Do you want the rounding happening after the Average, then do the *52/12 ?
Or do you want to do the Average*52/12 and THEN round it?

In my opinion the more accurate result is to do ALL the math with the actual complete numbers first, THEN round that result.
So the results Excel intially gave you actually are the most accurate numbers.

But Excel gives me these:
£535.33 £59.94 £33.48

But this is all just a matter of opinion, it's your sheet.

Yes it's an interesting one. Normally it would be done with a calculator, and I suppose you would do the whole calculation at once, so it would be a case of not using "Precision as Displayed". The only thing that makes it slightly awkward is that the spreadsheet displays the average in one cell, and then the monthly average in another cell, both with 2 decimal places. So someone could take a calculator and convert that average, and see that the answer isn't quite the same, and think that the spreadsheet was wrong. I'll just have to find out what people would prefer. At least I know what my options are now though, and how each option works.