Going potty - what am I missing ...sum () issue (1 Viewer)

[peskywinnets]

I'm going Radio GaGa here - what am I missing? ...sum () issue

I have a field on a report called LineCostExVAT & is calculated like this...

=Round([UnitCost]*[Qty]/1.2,2) ......(it's essentially showing the rounded down price of the goods with VAT removed)

...this bit works fine, for example if the UnitCost is £9.99, the value of LineCostExVAT shows £8.32

All good so far

BUT, on the same report, I also have a Sub Total field which is essentially the sum of all LineCostExVAT fields on the report , it's calculated like this...

=Sum([LineCostExVAT])

but it shows £8.33 .....so my problem is, the sum () is not summing correctly!!!! (i.e. it's showing £8.33 vs £8.32)

What am I missing?!!!!


here's a screen shot (orange box is the one showing the sub total incorrectly)...

 

[sneuberg]

9.99 divided by 1.2 is 8.325 before rounded. If you asked me to round that to two decimal places I'd say the answer is 8.33 but the round function http://www.techonthenet.com/access/functions/numeric/round.php rounds so the last digit is even. Maybe that's your problem. How is you sum being rounded?

I'm curious. What does the law say about rounding the VAT?

 

[peskywinnets]

9.99 divided by 1.2 is 8.325 before rounded. If you asked me to round that to two decimal places I'd say the answer is 8.33 but the round function http://www.techonthenet.com/access/functions/numeric/round.php rounds so the last digit is even. Maybe that's your problem. How is you sum being rounded?

I'm curious. What does the law say about rounding the VAT?

the law doesn't much care whether you round up or round down (i.e. to the nearest penny), but this isn't so much a question about which way to round, but more so why a sum of the LinecCostExVAT isn't summing properly!

 

[sneuberg]

the law doesn't much care whether you round up or round down (i.e. to the nearest penny), but this isn't so much a question about which way to round, but more so why a sum of the LinecCostExVAT isn't summing properly!

I think is a rounding issue. I think the sum is rounding it correctly while the round function is rounding incorrectly by design. What is limiting the sum to two digits?

Maybe you should increase the numeric of digits after the decimal in both places temporarily to see what's going on.

 

[peskywinnets]

I did that for the Line Cost it showed £8.320 for the Sub total it showed £8.330

!!!

 

[sneuberg]

I'm trying to replicate your problem, but on my system I'm getting 8.325 when I set it to =Round([UnitCost]*[Qty]/1.2,3) not 8.320 as you are. If I change the format of the text box to currency I get $8.33. So I guess the regional settings change the behavior.

Sorry I guess I won't be able to figure this out. I hope someone does as I'd really like to know what is going on here.

 

[peskywinnets]

Thanks for trying.

I still don't fully know what is going on, but I've got around it by doing all the rounding in the query that the report is linked to (vs. doing rounding in the report)

 

[Minty]

The round function sounds flawed to me. In accounting terms you normally use 4/5ths rounding. so as sneuberg pointed out the first result should be 8.33

I've seen weird behaviour like this before in reports and always end up forcing the results I need to the correct truncated values. I actually created a VAT function to do this on a complicated quote report but the maths is fairly simple;

Rounded value = (INT(YourValue*1000)+5)/1000

In your example this forces the result to 8.3300 so any subsequent additions / subtractions will be correct.

The Round function is apparently buggy as well http://www.experts-exchange.com/art...up-down-by-4-5-or-to-significant-figures.html
That helps

 

[peskywinnets]

There are two separate 'challenges' in play here. The first is getting access to round as intended (which is where I came in with this thread!)

The second is more 'presentation' to the customer.

For example ...if an item costs £9.99, the excluding VAT price is £8.325 ....and the VAT is £1.665 .....clearly both need rounding, but since the least significant number is 5, if you apply the same rounding rule, you'll end up with a sales receipt that looks wrong (£8.33 + £1.67 = £10.00!!) ....so the dilemma here is not just getting access to round, but also presenting the rounded figures in a way that's palette-able to the customer reading the resulting sales receipt.

 

[arnelgp]

decimal is more accurate:
cdec(format(([UnitCoist]*[Qty]/1.2),"0.00"))

 

[peskywinnets]

decimal is more accurate:
cdec(format(([UnitCost]*[Qty]/1.2),"0.00"))

Thanks...that's a nice one to know about, but how is it more accurate vs. say something like this

Round up, two decimal places ...... round(([UnitCost]*[Qty]/1.2)+0.000001,2)

Round down, two decimal places ...... round(([UnitCost]*[Qty]/1.2)-0.000001,2)


For a £9.99 in VAT value each of the above results in £8.33 & £8.32 respectively......I'm curious when it'd be best to use cdec instead?

 

[Gasman]

Why not round the VAT and subtract that from the gross price. Then sum the net price.?

 

[arnelgp]

since you are using lots of calculation involving money it would be wise to use Decimal field type, rather than Currency or Double. although it will not display the currency symbol, but you can manually put it by formatting the textbox control.

 

[peskywinnets]

Why not round the VAT and subtract that from the gross price. Then sum the net price.?

Because if you had an order with say 30 line items, the rounding errors would be significant.

the way I've decided to do this is to divide the Total Cost (i.e. the order total) by six then round that up ...this is the VAT aspect. I then subtract the VAT from the Total Cost to give the net sub total.

 

[Gasman]

Because if you had an order with say 30 line items, the rounding errors would be significant.

the way I've decided to do this is to divide the Total Cost (i.e. the order total) by six then round that up ...this is the VAT aspect. I then subtract the VAT from the Total Cost to give the net sub total.

I meant on each line. not on totals? The total would be the sum of the net prices. Or am I missing a key issue here.?

In your example the gross price is 9.99 and you calculate the net as 8.32 with VAT being 1.67. The vat is actually 1.665 and if you round it you get your 1.67.

This should remove your rounding errors as you are only rounding one value.

You then sum the net values to get your correct totals. If you sum the VAT as well and add that to the net, it should match the gross.?

It's been a while since I played around with values like this, so may be a little rusty, but I would still like to know why this would not work?

 

[peskywinnets]

I meant on each line. not on totals? The total would be the sum of the net prices. Or am I missing a key issue here.?

In your example the gross price is 9.99 and you calculate the net as 8.32 with VAT being 1.67. The vat is actually 1.665 and if you round it you get your 1.67.

This should remove your rounding errors as you are only rounding one value.

You then sum the net values to get your correct totals. If you sum the VAT as well and add that to the net, it should match the gross.?

It's been a while since I played around with values like this, so may be a little rusty, but I would still like to know why this would not work?

Ok, so let';s imagine if 9 different products were on the sales receipt, each costing £9.99, therefore the Order Total would need to show as £89.91 (9 x £9.99)

The rounded up Gross Total VAT for the order would be £14.99, but using your method, each product line total would be showing as £8.32....therefore the net subtotal would sum to be £74.88 (9 x £8.32)......

£74.88 (net sub total) + £14.99 (vat) = £89.87 ....which is incorrect (the order total should be £89.91)

 

[Gasman]

Ok, so let';s imagine if 9 different products were on the sales receipt, each costing £9.99, therefore the Order Total would need to show as £89.91 (9 x £9.99)

The rounded up Gross Total VAT for the order would be £14.99, but using your method, each product line total would be showing as £8.32....therefore the net subtotal would sum to be £74.88 (9 x £8.32)......

£74.88 (net sub total) + £14.99 (vat) = £89.87 ....which is incorrect (the order total should be £89.91)

No, you are missing the point. The only time you round is on the single VAT amount.

Here is an excel sheet.

Column 1 is Gross, Column 2 is net (not rounded), column 3 is VAT (rounded), column 4 is net (gross - rounded VAT)

So just totalling the values we get 89.91 for gross, 15.03 for VAT and 74.88 for net. Add VAT and net and we get 89.91.

The key to all this is just round one value. You can choose which one it is, you could round net and subtract that from gross, I chose VAT.

9.99	8.325	1.67	8.32
9.99	8.325	1.67	8.32
9.99	8.325	1.67	8.32
9.99	8.325	1.67	8.32
9.99	8.325	1.67	8.32
9.99	8.325	1.67	8.32
9.99	8.325	1.67	8.32
9.99	8.325	1.67	8.32
9.99	8.325	1.67	8.32

89.91	74.925	15.03	74.88

 

[peskywinnets]

So just totalling the values we get 89.91 for gross, 15.03 for VAT and 74.88 for net. Add VAT and net and we get 89.91.

But the VAT total for an £89.91 order is NOT correct at £15.03, it's £14.99 ...& if I showed it as £15.03, my customers would not be happy.

 

[Gasman]

Seems the rounding should be on the net value ?

http://www.vatcalculator.co.uk/

VAT calculation formula for VAT exclusion is the following: to calculate VAT having the gross amount you should divide the gross amount by 1 + VAT percentage (i.e. if it is 15%, then you should divide by 1.15), then subtract the gross amount, multiply by -1 and round to the closest value (including eurocents).

online VAT calculator
vatcalconline.com/

HMRC appear to think differently?

http://www.hmrc.gov.uk/manuals/vatrecmanual/vatrec12030.htm

 

[peskywinnets]

To calculate the vat, I'm dividing the gross amount by 1.2 & rounding up if .005p or above.

I then take this VAT & deduct it from the gross amount to yield the NET subtotal ....it all works, except like I say, the subtotal is right but it might not necessarily total up what is immediately listed above it for the line items!