Help with rounddown excel formula

I meant that we cannot know just what your formula involves because it uses cells in your worksheet. I suggested you test with a simple number

- 0.199 - to see if ROUNDDOWN works.

But I agree with others that it's not worth bothering about differences of a penny or so. And if you really want to pursue it then you need you to first work out which method your supplier is using to arrive at its VAT total.

While true, it is useful to understand how to do financial calcs in such a way that you don't run into rounding errors along the way. A penny on a single gas bill is nothing to worry about, a penny on everybody's bill is quite a different matter!

Thats solved the issue John. Now have it calculating correctly.

I can't quarrel with that. My point was that there is no one right way to deal with VAT on a utility bill and it's not always possible to work out from one bill which method they have used.

You very possibly know the methods by heart :) I looked them up to refresh my memory and in case anyone else cares:

"17.5.1 Calculation based on lines of goods or services

If you wish to work out the VAT separately for a line of goods or services, which are included with other goods or services in the same invoice, you should calculate the separate amounts of VAT by rounding in one of the following ways:

down to the nearest 0.1p - for example, 86.76p would be rounded down to 86.7p to the nearest 1p or 0.5p - for example, 86.76p would be rounded up to 87p

Whatever you decide, you must be consistent.

The final total amount of VAT payable may be rounded down to the nearest whole penny.

If you want to work out the VAT per unit or per article (for example, for use in price lists), you must work out the amounts in one of the following ways:

the nearest 1p or 0.5p - if you decide to do this, you must not round the VAT down to ?nil? on any unit or article that is liable at the standard or reduced rate, for example, if the VAT is £0.0024 it should be rounded to £0.005 (0.5p)"

I had previously emailed the company when I switched to them asking for what items and how many decimal points they used, but the guys on the other end only know whats on the statement and not how the calculations are arrived at. Issue is now resolved.

Truncated it. Rounding .758 to two decimals gives .76.

In general, if you're doing money, use integers. Floating point is only an approximation to reality. Enter all your numbers as pence and then display the

Yup, sure.

(although you can re-run the calcs yourself from the prices and quantities and get a feel for what they have done. Its not much practical use!)

I suppose the "optimal" option would be to construct your invoices such that they yield the lowest possible VAT total, and hope that your suppliers have not done likewise. That way you get the maximum clawback from HMRC (given that before paying VAT collected on sales to HMRC you deduct all VAT you paid on purchases).

Try this:

Multiply the unrounded number by 100, truncate it to an integer, and then divide by 100.

So your 0.758 becomes 75.8 and then 75 and then 0.75

So, if A1 contains your 0.758, put a formula in A2 (say): =INT(A1*100)/100

If you didn't want to use two cells, you could combine that with your original formula in a single cell -something like: =INT((K10+L10-O10)*$P$77*100)/100

Thanks