Incorrect Calculation

Hello,

I have noticed that Excel 2003 does not always calculate figures correctly.

I have been trying to total up an invoice by having a subtotal & a VAT cell
(VAT being the subtotal plus 17.5%). Being as VAT is 17.5%, the figure
calculates is sometimes a half penny.

When it comes to adding these two cells together, if they are £40.02 &
£10.03, I might get a total of 50.06. I assume that this is because,
although the cells are formatted to two decimal places, when excel adds two
cells together, it adds formulae rather than the figures.

Can anybody tell me give me more help & possibly suggest a solution so that
these two cells will add up correctly?

Thanks

Sam

Have a look in HELP index for ROUND

--
Don Guillett
SalesAid Software
[email protected]
"Sam Goddard" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I have noticed that Excel 2003 does not always calculate figures
correctly.
>
> I have been trying to total up an invoice by having a subtotal & a VAT
cell
> (VAT being the subtotal plus 17.5%). Being as VAT is 17.5%, the figure
> calculates is sometimes a half penny.
>
> When it comes to adding these two cells together, if they are £40.02 &
> £10.03, I might get a total of 50.06. I assume that this is because,
> although the cells are formatted to two decimal places, when excel adds
two
> cells together, it adds formulae rather than the figures.
>
> Can anybody tell me give me more help & possibly suggest a solution so
that
> these two cells will add up correctly?
>
> Thanks
>
> Sam
>
>

See

http://www.mcgimpsey.com/excel/pennyoff.html

--
Regards,

Peo Sjoblom


"Sam Goddard" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I have noticed that Excel 2003 does not always calculate figures
> correctly.
>
> I have been trying to total up an invoice by having a subtotal & a VAT
> cell (VAT being the subtotal plus 17.5%). Being as VAT is 17.5%, the
> figure calculates is sometimes a half penny.
>
> When it comes to adding these two cells together, if they are £40.02 &
> £10.03, I might get a total of 50.06. I assume that this is because,
> although the cells are formatted to two decimal places, when excel adds
> two cells together, it adds formulae rather than the figures.
>
> Can anybody tell me give me more help & possibly suggest a solution so
> that these two cells will add up correctly?
>
> Thanks
>
> Sam
>

I shall do, but how will this help in a way that taking the result to 2
decimal places will not?

Thanks for the suggestion.

Sam
"Don Guillett" <[email protected]> wrote in message
news:Oaw%[email protected]...
> Have a look in HELP index for ROUND
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Sam Goddard" <[email protected]> wrote in message
> news:[email protected]...
>> Hello,
>>
>> I have noticed that Excel 2003 does not always calculate figures
> correctly.
>>
>> I have been trying to total up an invoice by having a subtotal & a VAT
> cell
>> (VAT being the subtotal plus 17.5%). Being as VAT is 17.5%, the figure
>> calculates is sometimes a half penny.
>>
>> When it comes to adding these two cells together, if they are £40.02 &
>> £10.03, I might get a total of 50.06. I assume that this is because,
>> although the cells are formatted to two decimal places, when excel adds
> two
>> cells together, it adds formulae rather than the figures.
>>
>> Can anybody tell me give me more help & possibly suggest a solution so
> that
>> these two cells will add up correctly?
>>
>> Thanks
>>
>> Sam
>>
>>
>
>

The problem lies in the adding up.

If you buy something for £151.50 then the VAT is £22.56. However excel calulates it to 22.56383 even if it only displays 22.56

If you buy 5 of these items then the VAT should be £112.80 but excel (taking 5 decimal places) will calculate it as £112.82.

You avoid this by using the round(number,num_digits) function to cut off the excess decimals

so the VAT is actually =151.50-round(151.50/1.175,2)

If a value in an unformatted cell is 1.2468, then formatting it to two
decimal places will make it show 1.25. Formatting a cell does not affect the
underlying figures, so calculations on that cell will use the unformatted
number.

The ROUND function forces the use of a rounded figure. So it you have 1.2468
in cell A1, then a calculation that uses ROUND(A1,2) will use 1.25, and a
calculation using ROUND(A1,1) will use 1.2.

When calculating VAT, the VAT amount is rounded to the nearest penny below.
To do that you can use the ROUNDDOWN function.

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"Sam Goddard" <[email protected]> wrote in message
news:[email protected]...
> I shall do, but how will this help in a way that taking the result to 2
> decimal places will not?
>
> Thanks for the suggestion.
>
> Sam
> "Don Guillett" <[email protected]> wrote in message
> news:Oaw%[email protected]...
> > Have a look in HELP index for ROUND
> >
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > "Sam Goddard" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hello,
> >>
> >> I have noticed that Excel 2003 does not always calculate figures
> > correctly.
> >>
> >> I have been trying to total up an invoice by having a subtotal & a VAT
> > cell
> >> (VAT being the subtotal plus 17.5%). Being as VAT is 17.5%, the figure
> >> calculates is sometimes a half penny.
> >>
> >> When it comes to adding these two cells together, if they are £40.02 &
> >> £10.03, I might get a total of 50.06. I assume that this is because,
> >> although the cells are formatted to two decimal places, when excel adds
> > two
> >> cells together, it adds formulae rather than the figures.
> >>
> >> Can anybody tell me give me more help & possibly suggest a solution so
> > that
> >> these two cells will add up correctly?
> >>
> >> Thanks
> >>
> >> Sam
> >>
> >>
> >
> >
>
>

See:

http://www.mcgimpsey.com/excel/pennyoff.html


Regards,

Peo Sjoblom

"Sam Goddard" wrote:

> Hello,
>
> I have noticed that Excel 2003 does not always calculate figures correctly.
>
> I have been trying to total up an invoice by having a subtotal & a VAT cell
> (VAT being the subtotal plus 17.5%). Being as VAT is 17.5%, the figure
> calculates is sometimes a half penny.
>
> When it comes to adding these two cells together, if they are £40.02 &
> £10.03, I might get a total of 50.06. I assume that this is because,
> although the cells are formatted to two decimal places, when excel adds two
> cells together, it adds formulae rather than the figures.
>
> Can anybody tell me give me more help & possibly suggest a solution so that
> these two cells will add up correctly?
>
> Thanks
>
> Sam
>
>
>