Thread: why do numbers not add correctly in excel

When adding currency in an excel worksheet, the sums are not always correct .
They may be a penny higher or a penny lower than the correct amount. Some of
the amounts that are being added are obtained by using a percentage of
another amount.

Take a look at JE McGimpsey's site:
http://www.mcgimpsey.com/excel/pennyoff.html

kflag wrote:
>
> When adding currency in an excel worksheet, the sums are not always correct .
> They may be a penny higher or a penny lower than the correct amount. Some of
> the amounts that are being added are obtained by using a percentage of
> another amount.

--

Dave Peterson

It's because Excel holds a far greater precision than that displayed by the
format. You may see 1.50+1.50 and expect 3.00, but Excel may be holding a
true value of 1.5049+1.5049=3.098 or formatted to two decimals 3.01

To overcome this you can use the ROUND function on all your calculations, or
go to Tools>Options>Calculation>Precision as displayed

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"kflag" <kflag@discussions.microsoft.com> wrote in message
news:7D71A4D3-503E-4C71-8EDA-EEFDA3E1F151@microsoft.com...
> When adding currency in an excel worksheet, the sums are not always
> correct .
> They may be a penny higher or a penny lower than the correct amount. Some
> of
> the amounts that are being added are obtained by using a percentage of
> another amount.

or even 3.0098, but you hopefully get my drift!

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"Nick Hodge" <nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS> wrote in message
news:O1qzSqe9EHA.208@TK2MSFTNGP12.phx.gbl...
> It's because Excel holds a far greater precision than that displayed by
> the format. You may see 1.50+1.50 and expect 3.00, but Excel may be
> holding a true value of 1.5049+1.5049=3.098 or formatted to two decimals
> 3.01
>
> To overcome this you can use the ROUND function on all your calculations,
> or go to Tools>Options>Calculation>Precision as displayed
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
>
>
> "kflag" <kflag@discussions.microsoft.com> wrote in message
> news:7D71A4D3-503E-4C71-8EDA-EEFDA3E1F151@microsoft.com...
>> When adding currency in an excel worksheet, the sums are not always
>> correct .
>> They may be a penny higher or a penny lower than the correct amount.
>> Some of
>> the amounts that are being added are obtained by using a percentage of
>> another amount.
>
>