Excel problem

[colinicky]

I have a spread sheet for accounting but have a little problem in that the totals at the bottom of 3 columns do not add up to the total of the columns They are out by a penny which is driving my mother ( & hence me ) insane . I think it has to do with the vat in that each entry is worked to 2 decimal places ( & rounded accordingly ) but I think the spread sheat is adding them all up to the zillionth decimal place & then rounding the total .How can I stop this & get it to just add the figures that are on display ?

[tootricky]

={SUM(ROUND(A1:A4,2))}

or something like that. To get the {} press Ctrl+Shift+Enter once you have entered the formula. This is a range formula.

Cheers/Nic

[paul-gte]

click on the cell, go to format, select currency or number, select amount of decimal places ie 2 and that should do it, sorry if this is not exactly accurate as i cant check on this pc but you get the picture
cheers paul

[colinicky]

Originally Posted by paul-gte

click on the cell, go to format, select currency or number, select amount of decimal places ie 2 and that should do it, sorry if this is not exactly accurate as i cant check on this pc but you get the picture
cheers paul

Yep that is done already

[Adrian Kelland]

Originally Posted by paul-gte

click on the cell, go to format, select currency or number, select amount of decimal places ie 2 and that should do it, sorry if this is not exactly accurate as i cant check on this pc but you get the picture
cheers paul

Paul,

That is a display format. You may be looking at 3.33, but 3.33333333r is being stored. And more importantly, applied in formulae.

The Round() function is the way to go, round(3.33333,2) returns 3.33

Adrian

[colinicky]

Do I apply the round function to the column or the end cell ?

[colinicky]

If in the Column formulae where the heck do I put it in this :-

=CHOOSE(D5+1, 0, SUM(H5:Y5)*17.5%,0,0,0, SUM(H5:Y5)*5%,0,SUM(H5:Y5)/400*7)


TIA

[Adrian Kelland]

No idea Colin, I'd break it down into smaller sums/more columns. Also a good way of debugging. Small steps.

Adrian

[colinicky]

Originally Posted by Adrian Kelland

No idea Colin, I'd break it down into smaller sums/more columns. Also a good way of debugging. Small steps.

Adrian

That is the formula for working out 4 different rates of vat !

[big_si]

Does it work if you just put the round round the formula

=ROUND((CHOOSE(D5+1, 0, SUM(H5:Y5)*17.5%,0,0,0, SUM(H5:Y5)*5%,0,SUM(H5:Y5)/400*7)),2)


?