Excel auto sum question

[3200gt]

Excel guru's how do I correct this?
When I auto sum the net value and the VAT value to give a gross value with the cells set to two decimal places auto sum still reads more than two decimal places and consequently the gross figure is not the sum of the VAT and the Net.
ie,
Net value = x products at x £'s = £1483.295 (shows in cell as £1483.30)
VAT = (£1483.30 x 1.2)- £1483.30 = £296.66 (Shows in cell as £296.66)
Auto sum £1483.30 & £296.66 = £1779.96 (shows in cell as £1779.95)

Guess its a rounding conflict but how do I stop it?

[ATG]

You've only changed the way those numbers are displayed. You haven't actually rounded the numbers themselves. Excel is just summing the numbers you've passed to the sum function.

So you need to round each number before adding them. There'll be a function called "round" or something like that. You'll probably want to pay attention to whether it rounds fractions equal to our greater than 0.5 up or down.

[3200gt]

That's what I thought was the issue but I can't find where to correct it.

[Xerstead]

I think he's suggesting you change your Net Value equation to =ROUND(x products at x £'s, 2)
and the VAT to =ROUND((Net Value*0.2), 2)

Each will round the formula before the comma to two decimal places.
HTH.

[3200gt]

Ok that gave me a clue.
If I use =SUM(G28*E28,2)/1000
where G28 is the quantity of product and E28 is the price per 1000, it seems to work.

[Landlord]

The function you want is actually =FIXED()

[3200gt]

Nope, still something odd going on.
8,425 products at £181.00 per thousand = net value £1524.925. Formula = FIXED(8,425 x £181.00)/1000 shows in cell as £1524.93 (Cell set to 2 decimal places)
VAT value = £1524.93 x .2 = £304.986. Formula = SUM(£1524.93 x .2) shows in cell as £304.99 (Cell set to 2 decimal places)
Gross value = £1524.93 + £304.99 = £1829.92 Formula = SUM(£1524.93 + £304.99) shows in cell as £1829.91 (cell set to two decimal places.)

Obviously in the Formula text above it's cells not values.

So where's my error?

[danlightbulb]

You prob need to round the vat calc too.

Vat = round(cell x 0.2, 2)

Id use the round function specifying 2dp and then format the answer as currency.

Edited by danlightbulb on Tuesday 3rd May 12:47

[3200gt]

The Net value 3rd dec place is a 5, so should be rounded up, which it is. = 1524.93
The VAT Value 3rd dec place is a 6, so should be rounded up, which it is. = 304.99
sum of those two is 1829.92
But when I sum the total of the NET and VAT rounded up values the cell shows 1829.91.

So I'm assuming its taken the 2 values inc the 3rd dec place values and rounded down. All I want is the Gross value to be the sum of the displayed NET and VAT values. but I can't seem to get it to do it!

[3200gt]

Your added vat formula worked. I typed the last post before you added the formula. Thanks