Rounding question

I'm using Excel 2007 but 'save as' 97-2003 as colleagues have different 
versions.
All number formats are set to 2 decimal places.

I'm finding that percentage calculations are rounding up to 2 decimal places 
but when the result in currency is subtracted from another figure the sum is 
rounded down.

This gives results such as 6 - 3 = 4.

I'm sure there must be a simple answer but I can't find it.  Hoping someone 
here knows, as checking all simple calcuations is getting ridiculous !

Carrie

0
Carrie
2/13/2010 2:40:31 AM
excel 39879 articles. 2 followers. Follow

9 Replies
1400 Views

Similar Articles

[PageSpeed] 38

"Carrie" <pantscarolyn.smith2@ntlworld.com> wrote:
> All number formats are set to 2 decimal places.
> I'm finding that percentage calculations are rounding
> up to 2 decimal places but when the result in currency
> is subtracted from another figure the sum is rounded
> down.

I'm sure that is only by coincidence.

Formatting to 2 decimal places always results in rounding.  But note that 
rounding can go in either direction, depending on the result.  For example, 
1.4949 will round down to 1.49, whereas 1.495 will round up to 1.50.

Moreover, rounding due to formatting per se only changes the appearance of 
the value.  It does not change the value itself.  So, continuing with my 
example of 1.4949 in A1, which appears as 1.49, the formula =2*A1 will 
appear to result in 2.99, not 2.98 as you might incorrectly expect.  The 
actual value will be 1.9898.


> This gives results such as 6 - 3 = 4.

I presume you mean 6.00 - 3.00 = 4.00, since you said that numbers are 
formatted to 2 places.

Try formatting all the cells to many decimal places; you might need to 
display as many as 15 significant digits.  That might explain the problem in 
perception.

In any case, the solution is to explicitly round the value itself, not just 
the formatted number.  There are two ways to do that:  (a) use the ROUND 
function around your formulas, e.g. =ROUND(formula,2); and (b) set the 
"Precision as displayed" option under Tools > Options > Calculation (in 
Excel 2003).  But I deprecate the use of PAD (#b) for a variety of reasons.

One reason not to use PAD is your very situation:  you might want the 
percentage value to be accurate to as many decimal places as possible, which 
will vary with the magnitude of the percentage, whereas you might want the 
product of the percentage times a dollar amount to be rounded to the penny.

So you want to use ROUND selectively.  You would not round the expression 
that the percentage is derived from. You would probably round all 
expressions that represent dollars and cents.

If you still have questions, if would be help if provide some details, such 
as the formulas involved and the "exact" cell values to 15 significant 
digits.


----- original message -----

"Carrie" <pantscarolyn.smith2@ntlworld.com> wrote in message 
news:2D765BAA-4D9F-40B2-B7DC-114FCCB793FF@microsoft.com...
> I'm using Excel 2007 but 'save as' 97-2003 as colleagues have different 
> versions.
> All number formats are set to 2 decimal places.
>
> I'm finding that percentage calculations are rounding up to 2 decimal 
> places but when the result in currency is subtracted from another figure 
> the sum is rounded down.
>
> This gives results such as 6 - 3 = 4.
>
> I'm sure there must be a simple answer but I can't find it.  Hoping 
> someone here knows, as checking all simple calcuations is getting 
> ridiculous !
>
> Carrie
> 

0
Joe
2/13/2010 4:14:22 AM
I wrote:
> "Carrie" <pantscarolyn.smith2@ntlworld.com> wrote:
>> All number formats are set to 2 decimal places.
[....]
>> This gives results such as 6 - 3 = 4.
>
> I presume you mean 6.00 - 3.00 = 4.00, since you
> said that numbers are formatted to 2 places.

Well, I must admit:  I don't see how 6.00 - 3.00 could appear to equal 4.00.

So maybe you do mean 6 - 3 = 4 after all; that is, not all number formats 
are set to 2 decimal places ;-).

Suppose A1 is 6.49 and A2 is 2.50; then A1-A2 is about 3.99.  But formatted 
to zero decimal places, it would appear that 6 - 3 = 4.

If that is not what you meant, I would appreciate it if you provided the 
"exact" cell values to 15 significant digits, for my edification.


----- original message -----

"Joe User" <joeu2004> wrote in message 
news:%239znHMGrKHA.6064@TK2MSFTNGP02.phx.gbl...
> "Carrie" <pantscarolyn.smith2@ntlworld.com> wrote:
>> All number formats are set to 2 decimal places.
>> I'm finding that percentage calculations are rounding
>> up to 2 decimal places but when the result in currency
>> is subtracted from another figure the sum is rounded
>> down.
>
> I'm sure that is only by coincidence.
>
> Formatting to 2 decimal places always results in rounding.  But note that 
> rounding can go in either direction, depending on the result.  For 
> example, 1.4949 will round down to 1.49, whereas 1.495 will round up to 
> 1.50.
>
> Moreover, rounding due to formatting per se only changes the appearance of 
> the value.  It does not change the value itself.  So, continuing with my 
> example of 1.4949 in A1, which appears as 1.49, the formula =2*A1 will 
> appear to result in 2.99, not 2.98 as you might incorrectly expect.  The 
> actual value will be 1.9898.
>
>
>> This gives results such as 6 - 3 = 4.
>
> I presume you mean 6.00 - 3.00 = 4.00, since you said that numbers are 
> formatted to 2 places.
>
> Try formatting all the cells to many decimal places; you might need to 
> display as many as 15 significant digits.  That might explain the problem 
> in perception.
>
> In any case, the solution is to explicitly round the value itself, not 
> just the formatted number.  There are two ways to do that:  (a) use the 
> ROUND function around your formulas, e.g. =ROUND(formula,2); and (b) set 
> the "Precision as displayed" option under Tools > Options > Calculation 
> (in Excel 2003).  But I deprecate the use of PAD (#b) for a variety of 
> reasons.
>
> One reason not to use PAD is your very situation:  you might want the 
> percentage value to be accurate to as many decimal places as possible, 
> which will vary with the magnitude of the percentage, whereas you might 
> want the product of the percentage times a dollar amount to be rounded to 
> the penny.
>
> So you want to use ROUND selectively.  You would not round the expression 
> that the percentage is derived from. You would probably round all 
> expressions that represent dollars and cents.
>
> If you still have questions, if would be help if provide some details, 
> such as the formulas involved and the "exact" cell values to 15 
> significant digits.
>
>
> ----- original message -----
>
> "Carrie" <pantscarolyn.smith2@ntlworld.com> wrote in message 
> news:2D765BAA-4D9F-40B2-B7DC-114FCCB793FF@microsoft.com...
>> I'm using Excel 2007 but 'save as' 97-2003 as colleagues have different 
>> versions.
>> All number formats are set to 2 decimal places.
>>
>> I'm finding that percentage calculations are rounding up to 2 decimal 
>> places but when the result in currency is subtracted from another figure 
>> the sum is rounded down.
>>
>> This gives results such as 6 - 3 = 4.
>>
>> I'm sure there must be a simple answer but I can't find it.  Hoping 
>> someone here knows, as checking all simple calcuations is getting 
>> ridiculous !
>>
>> Carrie
>>
> 

0
Joe
2/13/2010 6:53:29 AM
Hello Carrie,

If you need show a rounded EXACT resultat with the smallest possible
absolute (or relative) error for the values to be summed up:
http://sulprobil.com/html/largest_remainder.html

Regards,
Bernd
0
Bernd
2/13/2010 12:15:10 PM
Thank you both for your responses which I think would work if I were a 
scientist or something much more clever, anyway!!

All I am trying to do is a simple invoice and statement.  The calculations 
are:

Sheet 1.  Invoice for �1481.26

Sheet 2.  Commission @ 10% of �1,350 = �135.00
                VAT @ 17.5% of �135.00 = �23.63
                Total = �158.63

Sheet 3.  Picks up figures from Sheets 1 & 2.
                Calculates �1481.26 - �148.63 = �1322.64

Whilst it may be accurate to 15 decimal places, a penny is really the lowest 
figure to be displayed on an invoice.  And although the number formats are 
set to 2 dp, this evidently only applies to the display!  I'd ideally like a 
way of creating an invoice template to round down to 2 decimal places, or 
even building in a background formula that checks all calculations make 
sense to 2 decimal places.

Hope this makes sense & thanks so much for helping,

Carrie

"Joe User" <joeu2004> wrote in message 
news:OZxpClHrKHA.4284@TK2MSFTNGP04.phx.gbl...
>I wrote:
>> "Carrie" <pantscarolyn.smith2@ntlworld.com> wrote:
>>> All number formats are set to 2 decimal places.
> [....]
>>> This gives results such as 6 - 3 = 4.
>>
>> I presume you mean 6.00 - 3.00 = 4.00, since you
>> said that numbers are formatted to 2 places.
>
> Well, I must admit:  I don't see how 6.00 - 3.00 could appear to equal 
> 4.00.
>
> So maybe you do mean 6 - 3 = 4 after all; that is, not all number formats 
> are set to 2 decimal places ;-).
>
> Suppose A1 is 6.49 and A2 is 2.50; then A1-A2 is about 3.99.  But 
> formatted to zero decimal places, it would appear that 6 - 3 = 4.
>
> If that is not what you meant, I would appreciate it if you provided the 
> "exact" cell values to 15 significant digits, for my edification.
>
>
> ----- original message -----
>
> "Joe User" <joeu2004> wrote in message 
> news:%239znHMGrKHA.6064@TK2MSFTNGP02.phx.gbl...
>> "Carrie" <pantscarolyn.smith2@ntlworld.com> wrote:
>>> All number formats are set to 2 decimal places.
>>> I'm finding that percentage calculations are rounding
>>> up to 2 decimal places but when the result in currency
>>> is subtracted from another figure the sum is rounded
>>> down.
>>
>> I'm sure that is only by coincidence.
>>
>> Formatting to 2 decimal places always results in rounding.  But note that 
>> rounding can go in either direction, depending on the result.  For 
>> example, 1.4949 will round down to 1.49, whereas 1.495 will round up to 
>> 1.50.
>>
>> Moreover, rounding due to formatting per se only changes the appearance 
>> of the value.  It does not change the value itself.  So, continuing with 
>> my example of 1.4949 in A1, which appears as 1.49, the formula =2*A1 will 
>> appear to result in 2.99, not 2.98 as you might incorrectly expect.  The 
>> actual value will be 1.9898.
>>
>>
>>> This gives results such as 6 - 3 = 4.
>>
>> I presume you mean 6.00 - 3.00 = 4.00, since you said that numbers are 
>> formatted to 2 places.
>>
>> Try formatting all the cells to many decimal places; you might need to 
>> display as many as 15 significant digits.  That might explain the problem 
>> in perception.
>>
>> In any case, the solution is to explicitly round the value itself, not 
>> just the formatted number.  There are two ways to do that:  (a) use the 
>> ROUND function around your formulas, e.g. =ROUND(formula,2); and (b) set 
>> the "Precision as displayed" option under Tools > Options > Calculation 
>> (in Excel 2003).  But I deprecate the use of PAD (#b) for a variety of 
>> reasons.
>>
>> One reason not to use PAD is your very situation:  you might want the 
>> percentage value to be accurate to as many decimal places as possible, 
>> which will vary with the magnitude of the percentage, whereas you might 
>> want the product of the percentage times a dollar amount to be rounded to 
>> the penny.
>>
>> So you want to use ROUND selectively.  You would not round the expression 
>> that the percentage is derived from. You would probably round all 
>> expressions that represent dollars and cents.
>>
>> If you still have questions, if would be help if provide some details, 
>> such as the formulas involved and the "exact" cell values to 15 
>> significant digits.
>>
>>
>> ----- original message -----
>>
>> "Carrie" <pantscarolyn.smith2@ntlworld.com> wrote in message 
>> news:2D765BAA-4D9F-40B2-B7DC-114FCCB793FF@microsoft.com...
>>> I'm using Excel 2007 but 'save as' 97-2003 as colleagues have different 
>>> versions.
>>> All number formats are set to 2 decimal places.
>>>
>>> I'm finding that percentage calculations are rounding up to 2 decimal 
>>> places but when the result in currency is subtracted from another figure 
>>> the sum is rounded down.
>>>
>>> This gives results such as 6 - 3 = 4.
>>>
>>> I'm sure there must be a simple answer but I can't find it.  Hoping 
>>> someone here knows, as checking all simple calcuations is getting 
>>> ridiculous !
>>>
>>> Carrie
>>>
>>
>

-- 
Carolyn.

Setting a good example for your children takes all the fun out of middle 
age. 

0
Carrie
2/13/2010 7:01:35 PM
"Carrie" <pantscarolyn.smith2@ntlworld.com> wrote:
> Sheet 3.  Picks up figures from Sheets 1 & 2.
>                Calculates �1481.26 - �148.63 = �1322.64

Oh, so that's where "6 - 3 = 4" comes from.  You would have gotten a usable 
answer much sooner if only you had included this example in the initial 
inquiry.


> I'd ideally like a way of creating an invoice template
> to round down to 2 decimal places

I providec the conceptual answer previously.  But now I can be specific.


> Sheet 2.  Commission @ 10% of �1,350 = �135.00
>                VAT @ 17.5% of �135.00 = �23.63
>                Total = �158.63

If 1350 is in A1, commission rate (10%) is in A2, VAT rate (17.5%) is A3, 
then:

B1, commission:  =ROUND(A1*A2,2)
B2, VAT:         =ROUND(B1*A3,2)
B3, total:       =B1+B2

Note:  It would be prudent to write B3 as =ROUND(B1+B2,2).  The explanation 
is probably more detail than you would want.  Suffice it to say:  it is 
rooted in the reason why IF(10.1-10=0.1,TRUE) returns FALSE(!).  Bottom 
line:  you probably want to use ROUND whenever the result should be dollars 
and cents or any other specific number of decimal places.

PS:  I know you wrote:  "I'd ideally like a way [...] to __round_down__ to 2 
decimal places".  But your VAT example suggests that you really want to 
__round__.  And it is more common to do just that.


> Sheet 1.  Invoice for �1481.26
[....]
> Sheet 3.  Picks up figures from Sheets 1 & 2.
>                Calculates �1481.26 - �148.63 = �1322.64

If 1481.26 is in A1 of Sheet1, then the Sheet3 formula is:

=Sheet1!A1 - Sheet2!B3

And again, it would be prudent to write =ROUND(Sheet1!A1-Sheet2!B3,2).

PS:  148.63 [sic] is an obvious typo.  You intended to write 158.63.


----- original message -----

"Carrie" <pantscarolyn.smith2@ntlworld.com> wrote in message 
news:2F45966B-A924-4540-8F9F-6334F62313BC@microsoft.com...
> Thank you both for your responses which I think would work if I were a 
> scientist or something much more clever, anyway!!
>
> All I am trying to do is a simple invoice and statement.  The calculations 
> are:
>
> Sheet 1.  Invoice for �1481.26
>
> Sheet 2.  Commission @ 10% of �1,350 = �135.00
>                VAT @ 17.5% of �135.00 = �23.63
>                Total = �158.63
>
> Sheet 3.  Picks up figures from Sheets 1 & 2.
>                Calculates �1481.26 - �148.63 = �1322.64
>
> Whilst it may be accurate to 15 decimal places, a penny is really the 
> lowest figure to be displayed on an invoice.  And although the number 
> formats are set to 2 dp, this evidently only applies to the display!  I'd 
> ideally like a way of creating an invoice template to round down to 2 
> decimal places, or even building in a background formula that checks all 
> calculations make sense to 2 decimal places.
>
> Hope this makes sense & thanks so much for helping,
>
> Carrie
>
> "Joe User" <joeu2004> wrote in message 
> news:OZxpClHrKHA.4284@TK2MSFTNGP04.phx.gbl...
>>I wrote:
>>> "Carrie" <pantscarolyn.smith2@ntlworld.com> wrote:
>>>> All number formats are set to 2 decimal places.
>> [....]
>>>> This gives results such as 6 - 3 = 4.
>>>
>>> I presume you mean 6.00 - 3.00 = 4.00, since you
>>> said that numbers are formatted to 2 places.
>>
>> Well, I must admit:  I don't see how 6.00 - 3.00 could appear to equal 
>> 4.00.
>>
>> So maybe you do mean 6 - 3 = 4 after all; that is, not all number formats 
>> are set to 2 decimal places ;-).
>>
>> Suppose A1 is 6.49 and A2 is 2.50; then A1-A2 is about 3.99.  But 
>> formatted to zero decimal places, it would appear that 6 - 3 = 4.
>>
>> If that is not what you meant, I would appreciate it if you provided the 
>> "exact" cell values to 15 significant digits, for my edification.
>>
>>
>> ----- original message -----
>>
>> "Joe User" <joeu2004> wrote in message 
>> news:%239znHMGrKHA.6064@TK2MSFTNGP02.phx.gbl...
>>> "Carrie" <pantscarolyn.smith2@ntlworld.com> wrote:
>>>> All number formats are set to 2 decimal places.
>>>> I'm finding that percentage calculations are rounding
>>>> up to 2 decimal places but when the result in currency
>>>> is subtracted from another figure the sum is rounded
>>>> down.
>>>
>>> I'm sure that is only by coincidence.
>>>
>>> Formatting to 2 decimal places always results in rounding.  But note 
>>> that rounding can go in either direction, depending on the result.  For 
>>> example, 1.4949 will round down to 1.49, whereas 1.495 will round up to 
>>> 1.50.
>>>
>>> Moreover, rounding due to formatting per se only changes the appearance 
>>> of the value.  It does not change the value itself.  So, continuing with 
>>> my example of 1.4949 in A1, which appears as 1.49, the formula =2*A1 
>>> will appear to result in 2.99, not 2.98 as you might incorrectly expect. 
>>> The actual value will be 1.9898.
>>>
>>>
>>>> This gives results such as 6 - 3 = 4.
>>>
>>> I presume you mean 6.00 - 3.00 = 4.00, since you said that numbers are 
>>> formatted to 2 places.
>>>
>>> Try formatting all the cells to many decimal places; you might need to 
>>> display as many as 15 significant digits.  That might explain the 
>>> problem in perception.
>>>
>>> In any case, the solution is to explicitly round the value itself, not 
>>> just the formatted number.  There are two ways to do that:  (a) use the 
>>> ROUND function around your formulas, e.g. =ROUND(formula,2); and (b) set 
>>> the "Precision as displayed" option under Tools > Options > Calculation 
>>> (in Excel 2003).  But I deprecate the use of PAD (#b) for a variety of 
>>> reasons.
>>>
>>> One reason not to use PAD is your very situation:  you might want the 
>>> percentage value to be accurate to as many decimal places as possible, 
>>> which will vary with the magnitude of the percentage, whereas you might 
>>> want the product of the percentage times a dollar amount to be rounded 
>>> to the penny.
>>>
>>> So you want to use ROUND selectively.  You would not round the 
>>> expression that the percentage is derived from. You would probably round 
>>> all expressions that represent dollars and cents.
>>>
>>> If you still have questions, if would be help if provide some details, 
>>> such as the formulas involved and the "exact" cell values to 15 
>>> significant digits.
>>>
>>>
>>> ----- original message -----
>>>
>>> "Carrie" <pantscarolyn.smith2@ntlworld.com> wrote in message 
>>> news:2D765BAA-4D9F-40B2-B7DC-114FCCB793FF@microsoft.com...
>>>> I'm using Excel 2007 but 'save as' 97-2003 as colleagues have different 
>>>> versions.
>>>> All number formats are set to 2 decimal places.
>>>>
>>>> I'm finding that percentage calculations are rounding up to 2 decimal 
>>>> places but when the result in currency is subtracted from another 
>>>> figure the sum is rounded down.
>>>>
>>>> This gives results such as 6 - 3 = 4.
>>>>
>>>> I'm sure there must be a simple answer but I can't find it.  Hoping 
>>>> someone here knows, as checking all simple calcuations is getting 
>>>> ridiculous !
>>>>
>>>> Carrie
>>>>
>>>
>>
>
> -- 
> Carolyn.
>
> Setting a good example for your children takes all the fun out of middle 
> age. 

0
Joe
2/13/2010 7:43:32 PM
PS....

"Carrie" <pantscarolyn.smith2@ntlworld.com> wrote:
> Whilst it may be accurate to 15 decimal places, a penny
> is really the lowest figure to be displayed on an invoice.

My request to see 15 significant digits was just for diagnostic purposes. 
That might be something to keep in mind for the future:  whenever you are 
having problems with numerical results, it might be useful to change formats 
so that you can see 15 significant digits, but only for the purpose of 
understanding the problem.

(Caveat:  There are instances where even that is not sufficient. 
Unfortunately, that is Excel's display limit.)

However, note that I say "significant digits", not "decimal places".  To see 
15 significant digits using a Number format (or equivalent), you might need 
to use different numbers of decimal places.  For example, for 1481.26, 
158.63 and 23.63, the number of decimal places would be 11, 12 and 13 
respectively.

(I prefer to use Scientific format.  Then, 14 decimal places always works. 
But many people do not understand numbers displayed in that format.)


----- original message -----

"Carrie" <pantscarolyn.smith2@ntlworld.com> wrote in message 
news:2F45966B-A924-4540-8F9F-6334F62313BC@microsoft.com...
> Thank you both for your responses which I think would work if I were a 
> scientist or something much more clever, anyway!!
>
> All I am trying to do is a simple invoice and statement.  The calculations 
> are:
>
> Sheet 1.  Invoice for �1481.26
>
> Sheet 2.  Commission @ 10% of �1,350 = �135.00
>                VAT @ 17.5% of �135.00 = �23.63
>                Total = �158.63
>
> Sheet 3.  Picks up figures from Sheets 1 & 2.
>                Calculates �1481.26 - �148.63 = �1322.64
>
> Whilst it may be accurate to 15 decimal places, a penny is really the 
> lowest figure to be displayed on an invoice.  And although the number 
> formats are set to 2 dp, this evidently only applies to the display!  I'd 
> ideally like a way of creating an invoice template to round down to 2 
> decimal places, or even building in a background formula that checks all 
> calculations make sense to 2 decimal places.
>
> Hope this makes sense & thanks so much for helping,
>
> Carrie
>
> "Joe User" <joeu2004> wrote in message 
> news:OZxpClHrKHA.4284@TK2MSFTNGP04.phx.gbl...
>>I wrote:
>>> "Carrie" <pantscarolyn.smith2@ntlworld.com> wrote:
>>>> All number formats are set to 2 decimal places.
>> [....]
>>>> This gives results such as 6 - 3 = 4.
>>>
>>> I presume you mean 6.00 - 3.00 = 4.00, since you
>>> said that numbers are formatted to 2 places.
>>
>> Well, I must admit:  I don't see how 6.00 - 3.00 could appear to equal 
>> 4.00.
>>
>> So maybe you do mean 6 - 3 = 4 after all; that is, not all number formats 
>> are set to 2 decimal places ;-).
>>
>> Suppose A1 is 6.49 and A2 is 2.50; then A1-A2 is about 3.99.  But 
>> formatted to zero decimal places, it would appear that 6 - 3 = 4.
>>
>> If that is not what you meant, I would appreciate it if you provided the 
>> "exact" cell values to 15 significant digits, for my edification.
>>
>>
>> ----- original message -----
>>
>> "Joe User" <joeu2004> wrote in message 
>> news:%239znHMGrKHA.6064@TK2MSFTNGP02.phx.gbl...
>>> "Carrie" <pantscarolyn.smith2@ntlworld.com> wrote:
>>>> All number formats are set to 2 decimal places.
>>>> I'm finding that percentage calculations are rounding
>>>> up to 2 decimal places but when the result in currency
>>>> is subtracted from another figure the sum is rounded
>>>> down.
>>>
>>> I'm sure that is only by coincidence.
>>>
>>> Formatting to 2 decimal places always results in rounding.  But note 
>>> that rounding can go in either direction, depending on the result.  For 
>>> example, 1.4949 will round down to 1.49, whereas 1.495 will round up to 
>>> 1.50.
>>>
>>> Moreover, rounding due to formatting per se only changes the appearance 
>>> of the value.  It does not change the value itself.  So, continuing with 
>>> my example of 1.4949 in A1, which appears as 1.49, the formula =2*A1 
>>> will appear to result in 2.99, not 2.98 as you might incorrectly expect. 
>>> The actual value will be 1.9898.
>>>
>>>
>>>> This gives results such as 6 - 3 = 4.
>>>
>>> I presume you mean 6.00 - 3.00 = 4.00, since you said that numbers are 
>>> formatted to 2 places.
>>>
>>> Try formatting all the cells to many decimal places; you might need to 
>>> display as many as 15 significant digits.  That might explain the 
>>> problem in perception.
>>>
>>> In any case, the solution is to explicitly round the value itself, not 
>>> just the formatted number.  There are two ways to do that:  (a) use the 
>>> ROUND function around your formulas, e.g. =ROUND(formula,2); and (b) set 
>>> the "Precision as displayed" option under Tools > Options > Calculation 
>>> (in Excel 2003).  But I deprecate the use of PAD (#b) for a variety of 
>>> reasons.
>>>
>>> One reason not to use PAD is your very situation:  you might want the 
>>> percentage value to be accurate to as many decimal places as possible, 
>>> which will vary with the magnitude of the percentage, whereas you might 
>>> want the product of the percentage times a dollar amount to be rounded 
>>> to the penny.
>>>
>>> So you want to use ROUND selectively.  You would not round the 
>>> expression that the percentage is derived from. You would probably round 
>>> all expressions that represent dollars and cents.
>>>
>>> If you still have questions, if would be help if provide some details, 
>>> such as the formulas involved and the "exact" cell values to 15 
>>> significant digits.
>>>
>>>
>>> ----- original message -----
>>>
>>> "Carrie" <pantscarolyn.smith2@ntlworld.com> wrote in message 
>>> news:2D765BAA-4D9F-40B2-B7DC-114FCCB793FF@microsoft.com...
>>>> I'm using Excel 2007 but 'save as' 97-2003 as colleagues have different 
>>>> versions.
>>>> All number formats are set to 2 decimal places.
>>>>
>>>> I'm finding that percentage calculations are rounding up to 2 decimal 
>>>> places but when the result in currency is subtracted from another 
>>>> figure the sum is rounded down.
>>>>
>>>> This gives results such as 6 - 3 = 4.
>>>>
>>>> I'm sure there must be a simple answer but I can't find it.  Hoping 
>>>> someone here knows, as checking all simple calcuations is getting 
>>>> ridiculous !
>>>>
>>>> Carrie
>>>>
>>>
>>
>
> -- 
> Carolyn.
>
> Setting a good example for your children takes all the fun out of middle 
> age. 

0
Joe
2/13/2010 8:13:00 PM
Very many thanks for your help, Joe.  I think I get it now!!
It would be nice to get Excel to default to rounding, however I'll take one 
step at a time...
Best regards,
Carrie

"Joe User" <joeu2004> wrote in message 
news:%23gsfVTOrKHA.3344@TK2MSFTNGP06.phx.gbl...
> "Carrie" <pantscarolyn.smith2@ntlworld.com> wrote:
>> Sheet 3.  Picks up figures from Sheets 1 & 2.
>>                Calculates �1481.26 - �148.63 = �1322.64
>
> Oh, so that's where "6 - 3 = 4" comes from.  You would have gotten a 
> usable answer much sooner if only you had included this example in the 
> initial inquiry.
>
>
>> I'd ideally like a way of creating an invoice template
>> to round down to 2 decimal places
>
> I providec the conceptual answer previously.  But now I can be specific.
>
>
>> Sheet 2.  Commission @ 10% of �1,350 = �135.00
>>                VAT @ 17.5% of �135.00 = �23.63
>>                Total = �158.63
>
> If 1350 is in A1, commission rate (10%) is in A2, VAT rate (17.5%) is A3, 
> then:
>
> B1, commission:  =ROUND(A1*A2,2)
> B2, VAT:         =ROUND(B1*A3,2)
> B3, total:       =B1+B2
>
> Note:  It would be prudent to write B3 as =ROUND(B1+B2,2).  The 
> explanation is probably more detail than you would want.  Suffice it to 
> say:  it is rooted in the reason why IF(10.1-10=0.1,TRUE) returns 
> FALSE(!).  Bottom line:  you probably want to use ROUND whenever the 
> result should be dollars and cents or any other specific number of decimal 
> places.
>
> PS:  I know you wrote:  "I'd ideally like a way [...] to __round_down__ to 
> 2 decimal places".  But your VAT example suggests that you really want to 
> __round__.  And it is more common to do just that.
>
>
>> Sheet 1.  Invoice for �1481.26
> [....]
>> Sheet 3.  Picks up figures from Sheets 1 & 2.
>>                Calculates �1481.26 - �148.63 = �1322.64
>
> If 1481.26 is in A1 of Sheet1, then the Sheet3 formula is:
>
> =Sheet1!A1 - Sheet2!B3
>
> And again, it would be prudent to write =ROUND(Sheet1!A1-Sheet2!B3,2).
>
> PS:  148.63 [sic] is an obvious typo.  You intended to write 158.63.
>
>
> ----- original message -----
>
> "Carrie" <pantscarolyn.smith2@ntlworld.com> wrote in message 
> news:2F45966B-A924-4540-8F9F-6334F62313BC@microsoft.com...
>> Thank you both for your responses which I think would work if I were a 
>> scientist or something much more clever, anyway!!
>>
>> All I am trying to do is a simple invoice and statement.  The 
>> calculations are:
>>
>> Sheet 1.  Invoice for �1481.26
>>
>> Sheet 2.  Commission @ 10% of �1,350 = �135.00
>>                VAT @ 17.5% of �135.00 = �23.63
>>                Total = �158.63
>>
>> Sheet 3.  Picks up figures from Sheets 1 & 2.
>>                Calculates �1481.26 - �148.63 = �1322.64
>>
>> Whilst it may be accurate to 15 decimal places, a penny is really the 
>> lowest figure to be displayed on an invoice.  And although the number 
>> formats are set to 2 dp, this evidently only applies to the display!  I'd 
>> ideally like a way of creating an invoice template to round down to 2 
>> decimal places, or even building in a background formula that checks all 
>> calculations make sense to 2 decimal places.
>>
>> Hope this makes sense & thanks so much for helping,
>>
>> Carrie
>>
>> "Joe User" <joeu2004> wrote in message 
>> news:OZxpClHrKHA.4284@TK2MSFTNGP04.phx.gbl...
>>>I wrote:
>>>> "Carrie" <pantscarolyn.smith2@ntlworld.com> wrote:
>>>>> All number formats are set to 2 decimal places.
>>> [....]
>>>>> This gives results such as 6 - 3 = 4.
>>>>
>>>> I presume you mean 6.00 - 3.00 = 4.00, since you
>>>> said that numbers are formatted to 2 places.
>>>
>>> Well, I must admit:  I don't see how 6.00 - 3.00 could appear to equal 
>>> 4.00.
>>>
>>> So maybe you do mean 6 - 3 = 4 after all; that is, not all number 
>>> formats are set to 2 decimal places ;-).
>>>
>>> Suppose A1 is 6.49 and A2 is 2.50; then A1-A2 is about 3.99.  But 
>>> formatted to zero decimal places, it would appear that 6 - 3 = 4.
>>>
>>> If that is not what you meant, I would appreciate it if you provided the 
>>> "exact" cell values to 15 significant digits, for my edification.
>>>
>>>
>>> ----- original message -----
>>>
>>> "Joe User" <joeu2004> wrote in message 
>>> news:%239znHMGrKHA.6064@TK2MSFTNGP02.phx.gbl...
>>>> "Carrie" <pantscarolyn.smith2@ntlworld.com> wrote:
>>>>> All number formats are set to 2 decimal places.
>>>>> I'm finding that percentage calculations are rounding
>>>>> up to 2 decimal places but when the result in currency
>>>>> is subtracted from another figure the sum is rounded
>>>>> down.
>>>>
>>>> I'm sure that is only by coincidence.
>>>>
>>>> Formatting to 2 decimal places always results in rounding.  But note 
>>>> that rounding can go in either direction, depending on the result.  For 
>>>> example, 1.4949 will round down to 1.49, whereas 1.495 will round up to 
>>>> 1.50.
>>>>
>>>> Moreover, rounding due to formatting per se only changes the appearance 
>>>> of the value.  It does not change the value itself.  So, continuing 
>>>> with my example of 1.4949 in A1, which appears as 1.49, the formula 
>>>> =2*A1 will appear to result in 2.99, not 2.98 as you might incorrectly 
>>>> expect. The actual value will be 1.9898.
>>>>
>>>>
>>>>> This gives results such as 6 - 3 = 4.
>>>>
>>>> I presume you mean 6.00 - 3.00 = 4.00, since you said that numbers are 
>>>> formatted to 2 places.
>>>>
>>>> Try formatting all the cells to many decimal places; you might need to 
>>>> display as many as 15 significant digits.  That might explain the 
>>>> problem in perception.
>>>>
>>>> In any case, the solution is to explicitly round the value itself, not 
>>>> just the formatted number.  There are two ways to do that:  (a) use the 
>>>> ROUND function around your formulas, e.g. =ROUND(formula,2); and (b) 
>>>> set the "Precision as displayed" option under Tools > Options > 
>>>> Calculation (in Excel 2003).  But I deprecate the use of PAD (#b) for a 
>>>> variety of reasons.
>>>>
>>>> One reason not to use PAD is your very situation:  you might want the 
>>>> percentage value to be accurate to as many decimal places as possible, 
>>>> which will vary with the magnitude of the percentage, whereas you might 
>>>> want the product of the percentage times a dollar amount to be rounded 
>>>> to the penny.
>>>>
>>>> So you want to use ROUND selectively.  You would not round the 
>>>> expression that the percentage is derived from. You would probably 
>>>> round all expressions that represent dollars and cents.
>>>>
>>>> If you still have questions, if would be help if provide some details, 
>>>> such as the formulas involved and the "exact" cell values to 15 
>>>> significant digits.
>>>>
>>>>
>>>> ----- original message -----
>>>>
>>>> "Carrie" <pantscarolyn.smith2@ntlworld.com> wrote in message 
>>>> news:2D765BAA-4D9F-40B2-B7DC-114FCCB793FF@microsoft.com...
>>>>> I'm using Excel 2007 but 'save as' 97-2003 as colleagues have 
>>>>> different versions.
>>>>> All number formats are set to 2 decimal places.
>>>>>
>>>>> I'm finding that percentage calculations are rounding up to 2 decimal 
>>>>> places but when the result in currency is subtracted from another 
>>>>> figure the sum is rounded down.
>>>>>
>>>>> This gives results such as 6 - 3 = 4.
>>>>>
>>>>> I'm sure there must be a simple answer but I can't find it.  Hoping 
>>>>> someone here knows, as checking all simple calcuations is getting 
>>>>> ridiculous !
>>>>>
>>>>> Carrie
>>>>>
>>>>
>>>

0
Carrie
2/14/2010 12:19:51 PM
You can ask Excel to "default to rounding". There's an option to set 
"precision as displayed". Sometimes it brings more problems than it solves, 
however.

Regards,
Fred

"Carrie" <pantscarolyn.smith2@ntlworld.com> wrote in message 
news:94B04CCE-1EA4-4244-9CB4-B8C102758255@microsoft.com...
> Very many thanks for your help, Joe.  I think I get it now!!
> It would be nice to get Excel to default to rounding, however I'll take 
> one step at a time...
> Best regards,
> Carrie
>
> "Joe User" <joeu2004> wrote in message 
> news:%23gsfVTOrKHA.3344@TK2MSFTNGP06.phx.gbl...
>> "Carrie" <pantscarolyn.smith2@ntlworld.com> wrote:
>>> Sheet 3.  Picks up figures from Sheets 1 & 2.
>>>                Calculates �1481.26 - �148.63 = �1322.64
>>
>> Oh, so that's where "6 - 3 = 4" comes from.  You would have gotten a 
>> usable answer much sooner if only you had included this example in the 
>> initial inquiry.
>>
>>
>>> I'd ideally like a way of creating an invoice template
>>> to round down to 2 decimal places
>>
>> I providec the conceptual answer previously.  But now I can be specific.
>>
>>
>>> Sheet 2.  Commission @ 10% of �1,350 = �135.00
>>>                VAT @ 17.5% of �135.00 = �23.63
>>>                Total = �158.63
>>
>> If 1350 is in A1, commission rate (10%) is in A2, VAT rate (17.5%) is A3, 
>> then:
>>
>> B1, commission:  =ROUND(A1*A2,2)
>> B2, VAT:         =ROUND(B1*A3,2)
>> B3, total:       =B1+B2
>>
>> Note:  It would be prudent to write B3 as =ROUND(B1+B2,2).  The 
>> explanation is probably more detail than you would want.  Suffice it to 
>> say:  it is rooted in the reason why IF(10.1-10=0.1,TRUE) returns 
>> FALSE(!).  Bottom line:  you probably want to use ROUND whenever the 
>> result should be dollars and cents or any other specific number of 
>> decimal places.
>>
>> PS:  I know you wrote:  "I'd ideally like a way [...] to __round_down__ 
>> to 2 decimal places".  But your VAT example suggests that you really want 
>> to __round__.  And it is more common to do just that.
>>
>>
>>> Sheet 1.  Invoice for �1481.26
>> [....]
>>> Sheet 3.  Picks up figures from Sheets 1 & 2.
>>>                Calculates �1481.26 - �148.63 = �1322.64
>>
>> If 1481.26 is in A1 of Sheet1, then the Sheet3 formula is:
>>
>> =Sheet1!A1 - Sheet2!B3
>>
>> And again, it would be prudent to write =ROUND(Sheet1!A1-Sheet2!B3,2).
>>
>> PS:  148.63 [sic] is an obvious typo.  You intended to write 158.63.
>>
>>
>> ----- original message -----
>>
>> "Carrie" <pantscarolyn.smith2@ntlworld.com> wrote in message 
>> news:2F45966B-A924-4540-8F9F-6334F62313BC@microsoft.com...
>>> Thank you both for your responses which I think would work if I were a 
>>> scientist or something much more clever, anyway!!
>>>
>>> All I am trying to do is a simple invoice and statement.  The 
>>> calculations are:
>>>
>>> Sheet 1.  Invoice for �1481.26
>>>
>>> Sheet 2.  Commission @ 10% of �1,350 = �135.00
>>>                VAT @ 17.5% of �135.00 = �23.63
>>>                Total = �158.63
>>>
>>> Sheet 3.  Picks up figures from Sheets 1 & 2.
>>>                Calculates �1481.26 - �148.63 = �1322.64
>>>
>>> Whilst it may be accurate to 15 decimal places, a penny is really the 
>>> lowest figure to be displayed on an invoice.  And although the number 
>>> formats are set to 2 dp, this evidently only applies to the display! 
>>> I'd ideally like a way of creating an invoice template to round down to 
>>> 2 decimal places, or even building in a background formula that checks 
>>> all calculations make sense to 2 decimal places.
>>>
>>> Hope this makes sense & thanks so much for helping,
>>>
>>> Carrie
>>>
>>> "Joe User" <joeu2004> wrote in message 
>>> news:OZxpClHrKHA.4284@TK2MSFTNGP04.phx.gbl...
>>>>I wrote:
>>>>> "Carrie" <pantscarolyn.smith2@ntlworld.com> wrote:
>>>>>> All number formats are set to 2 decimal places.
>>>> [....]
>>>>>> This gives results such as 6 - 3 = 4.
>>>>>
>>>>> I presume you mean 6.00 - 3.00 = 4.00, since you
>>>>> said that numbers are formatted to 2 places.
>>>>
>>>> Well, I must admit:  I don't see how 6.00 - 3.00 could appear to equal 
>>>> 4.00.
>>>>
>>>> So maybe you do mean 6 - 3 = 4 after all; that is, not all number 
>>>> formats are set to 2 decimal places ;-).
>>>>
>>>> Suppose A1 is 6.49 and A2 is 2.50; then A1-A2 is about 3.99.  But 
>>>> formatted to zero decimal places, it would appear that 6 - 3 = 4.
>>>>
>>>> If that is not what you meant, I would appreciate it if you provided 
>>>> the "exact" cell values to 15 significant digits, for my edification.
>>>>
>>>>
>>>> ----- original message -----
>>>>
>>>> "Joe User" <joeu2004> wrote in message 
>>>> news:%239znHMGrKHA.6064@TK2MSFTNGP02.phx.gbl...
>>>>> "Carrie" <pantscarolyn.smith2@ntlworld.com> wrote:
>>>>>> All number formats are set to 2 decimal places.
>>>>>> I'm finding that percentage calculations are rounding
>>>>>> up to 2 decimal places but when the result in currency
>>>>>> is subtracted from another figure the sum is rounded
>>>>>> down.
>>>>>
>>>>> I'm sure that is only by coincidence.
>>>>>
>>>>> Formatting to 2 decimal places always results in rounding.  But note 
>>>>> that rounding can go in either direction, depending on the result. 
>>>>> For example, 1.4949 will round down to 1.49, whereas 1.495 will round 
>>>>> up to 1.50.
>>>>>
>>>>> Moreover, rounding due to formatting per se only changes the 
>>>>> appearance of the value.  It does not change the value itself.  So, 
>>>>> continuing with my example of 1.4949 in A1, which appears as 1.49, the 
>>>>> formula =2*A1 will appear to result in 2.99, not 2.98 as you might 
>>>>> incorrectly expect. The actual value will be 1.9898.
>>>>>
>>>>>
>>>>>> This gives results such as 6 - 3 = 4.
>>>>>
>>>>> I presume you mean 6.00 - 3.00 = 4.00, since you said that numbers are 
>>>>> formatted to 2 places.
>>>>>
>>>>> Try formatting all the cells to many decimal places; you might need to 
>>>>> display as many as 15 significant digits.  That might explain the 
>>>>> problem in perception.
>>>>>
>>>>> In any case, the solution is to explicitly round the value itself, not 
>>>>> just the formatted number.  There are two ways to do that:  (a) use 
>>>>> the ROUND function around your formulas, e.g. =ROUND(formula,2); and 
>>>>> (b) set the "Precision as displayed" option under Tools > Options > 
>>>>> Calculation (in Excel 2003).  But I deprecate the use of PAD (#b) for 
>>>>> a variety of reasons.
>>>>>
>>>>> One reason not to use PAD is your very situation:  you might want the 
>>>>> percentage value to be accurate to as many decimal places as possible, 
>>>>> which will vary with the magnitude of the percentage, whereas you 
>>>>> might want the product of the percentage times a dollar amount to be 
>>>>> rounded to the penny.
>>>>>
>>>>> So you want to use ROUND selectively.  You would not round the 
>>>>> expression that the percentage is derived from. You would probably 
>>>>> round all expressions that represent dollars and cents.
>>>>>
>>>>> If you still have questions, if would be help if provide some details, 
>>>>> such as the formulas involved and the "exact" cell values to 15 
>>>>> significant digits.
>>>>>
>>>>>
>>>>> ----- original message -----
>>>>>
>>>>> "Carrie" <pantscarolyn.smith2@ntlworld.com> wrote in message 
>>>>> news:2D765BAA-4D9F-40B2-B7DC-114FCCB793FF@microsoft.com...
>>>>>> I'm using Excel 2007 but 'save as' 97-2003 as colleagues have 
>>>>>> different versions.
>>>>>> All number formats are set to 2 decimal places.
>>>>>>
>>>>>> I'm finding that percentage calculations are rounding up to 2 decimal 
>>>>>> places but when the result in currency is subtracted from another 
>>>>>> figure the sum is rounded down.
>>>>>>
>>>>>> This gives results such as 6 - 3 = 4.
>>>>>>
>>>>>> I'm sure there must be a simple answer but I can't find it.  Hoping 
>>>>>> someone here knows, as checking all simple calcuations is getting 
>>>>>> ridiculous !
>>>>>>
>>>>>> Carrie
>>>>>>
>>>>>
>>>>
> 

0
Fred
2/14/2010 1:12:06 PM
"Carrie" <pantscarolyn.smith2@ntlworld.com> wrote:
> It would be nice to get Excel to default to rounding

Well, I did mention the "Precision as displayed" option (under Tools > 
Options > Calculation in Excel 2003) in my first response.

But if you try setting it, be sure to make a copy of your Excel file first.

First, PAD applies only on cells that have a numeric format that specifies 
the number of decimal places.  So, for example, it does not apply to cells 
formatted as General.

Second, PAD applies to all cells in the Excel file.  So it is very easy to 
set PAD and make inadvertent changes on inactive worksheets -- that is, 
other than the worksheet you are looking at.  Because you are not looking at 
those worksheets, it might be a long time before discover any undesirable 
consequences; and by then, you might not realize that setting PAD is the 
root cause of the problem.

PAD will permanently change any constant cell value in a cell that has a 
numeric format with decimal places.  The change cannot be undone. 
Consequently, it would behoove you to review all of your constant-valued 
cells on all worksheets before setting PAD.

For example, it is not uncommon to format interest rate as Percentage with 2 
decimal places, but to enter a constant percentage with more decimal places. 
Setting PAD will round the interest rate, which can irreversibly alter every 
calculation that depends on that interest rate directly or indirectly.

Finally, because PAD affects all cells with a numeric format with decimal 
places, it might inadvertently alter cells that show intermediate values 
that are intended to be maintained with greater precision.

For example, it is not uncommon to show interest and principal paid and 
remaining balance on a per-payment basis in an amortization schedule, using 
a numeric format with 2 decimal places.  But the design of the amortization 
schedule works only if those underlying values retain their fullest 
precision.  Once you set PAD, the amortization schedule might no longer work 
because the underlying values, not just the displayed value, are rounded. 
Fortunately, that unwanted effect is reversible, simply by disabling PAD.


----- original message -----

"Carrie" <pantscarolyn.smith2@ntlworld.com> wrote in message 
news:94B04CCE-1EA4-4244-9CB4-B8C102758255@microsoft.com...
> Very many thanks for your help, Joe.  I think I get it now!!
> It would be nice to get Excel to default to rounding, however I'll take 
> one step at a time...
> Best regards,
> Carrie
>
> "Joe User" <joeu2004> wrote in message 
> news:%23gsfVTOrKHA.3344@TK2MSFTNGP06.phx.gbl...
>> "Carrie" <pantscarolyn.smith2@ntlworld.com> wrote:
>>> Sheet 3.  Picks up figures from Sheets 1 & 2.
>>>                Calculates �1481.26 - �148.63 = �1322.64
>>
>> Oh, so that's where "6 - 3 = 4" comes from.  You would have gotten a 
>> usable answer much sooner if only you had included this example in the 
>> initial inquiry.
>>
>>
>>> I'd ideally like a way of creating an invoice template
>>> to round down to 2 decimal places
>>
>> I providec the conceptual answer previously.  But now I can be specific.
>>
>>
>>> Sheet 2.  Commission @ 10% of �1,350 = �135.00
>>>                VAT @ 17.5% of �135.00 = �23.63
>>>                Total = �158.63
>>
>> If 1350 is in A1, commission rate (10%) is in A2, VAT rate (17.5%) is A3, 
>> then:
>>
>> B1, commission:  =ROUND(A1*A2,2)
>> B2, VAT:         =ROUND(B1*A3,2)
>> B3, total:       =B1+B2
>>
>> Note:  It would be prudent to write B3 as =ROUND(B1+B2,2).  The 
>> explanation is probably more detail than you would want.  Suffice it to 
>> say:  it is rooted in the reason why IF(10.1-10=0.1,TRUE) returns 
>> FALSE(!).  Bottom line:  you probably want to use ROUND whenever the 
>> result should be dollars and cents or any other specific number of 
>> decimal places.
>>
>> PS:  I know you wrote:  "I'd ideally like a way [...] to __round_down__ 
>> to 2 decimal places".  But your VAT example suggests that you really want 
>> to __round__.  And it is more common to do just that.
>>
>>
>>> Sheet 1.  Invoice for �1481.26
>> [....]
>>> Sheet 3.  Picks up figures from Sheets 1 & 2.
>>>                Calculates �1481.26 - �148.63 = �1322.64
>>
>> If 1481.26 is in A1 of Sheet1, then the Sheet3 formula is:
>>
>> =Sheet1!A1 - Sheet2!B3
>>
>> And again, it would be prudent to write =ROUND(Sheet1!A1-Sheet2!B3,2).
>>
>> PS:  148.63 [sic] is an obvious typo.  You intended to write 158.63.
>>
>>
>> ----- original message -----
>>
>> "Carrie" <pantscarolyn.smith2@ntlworld.com> wrote in message 
>> news:2F45966B-A924-4540-8F9F-6334F62313BC@microsoft.com...
>>> Thank you both for your responses which I think would work if I were a 
>>> scientist or something much more clever, anyway!!
>>>
>>> All I am trying to do is a simple invoice and statement.  The 
>>> calculations are:
>>>
>>> Sheet 1.  Invoice for �1481.26
>>>
>>> Sheet 2.  Commission @ 10% of �1,350 = �135.00
>>>                VAT @ 17.5% of �135.00 = �23.63
>>>                Total = �158.63
>>>
>>> Sheet 3.  Picks up figures from Sheets 1 & 2.
>>>                Calculates �1481.26 - �148.63 = �1322.64
>>>
>>> Whilst it may be accurate to 15 decimal places, a penny is really the 
>>> lowest figure to be displayed on an invoice.  And although the number 
>>> formats are set to 2 dp, this evidently only applies to the display! 
>>> I'd ideally like a way of creating an invoice template to round down to 
>>> 2 decimal places, or even building in a background formula that checks 
>>> all calculations make sense to 2 decimal places.
>>>
>>> Hope this makes sense & thanks so much for helping,
>>>
>>> Carrie
>>>
>>> "Joe User" <joeu2004> wrote in message 
>>> news:OZxpClHrKHA.4284@TK2MSFTNGP04.phx.gbl...
>>>>I wrote:
>>>>> "Carrie" <pantscarolyn.smith2@ntlworld.com> wrote:
>>>>>> All number formats are set to 2 decimal places.
>>>> [....]
>>>>>> This gives results such as 6 - 3 = 4.
>>>>>
>>>>> I presume you mean 6.00 - 3.00 = 4.00, since you
>>>>> said that numbers are formatted to 2 places.
>>>>
>>>> Well, I must admit:  I don't see how 6.00 - 3.00 could appear to equal 
>>>> 4.00.
>>>>
>>>> So maybe you do mean 6 - 3 = 4 after all; that is, not all number 
>>>> formats are set to 2 decimal places ;-).
>>>>
>>>> Suppose A1 is 6.49 and A2 is 2.50; then A1-A2 is about 3.99.  But 
>>>> formatted to zero decimal places, it would appear that 6 - 3 = 4.
>>>>
>>>> If that is not what you meant, I would appreciate it if you provided 
>>>> the "exact" cell values to 15 significant digits, for my edification.
>>>>
>>>>
>>>> ----- original message -----
>>>>
>>>> "Joe User" <joeu2004> wrote in message 
>>>> news:%239znHMGrKHA.6064@TK2MSFTNGP02.phx.gbl...
>>>>> "Carrie" <pantscarolyn.smith2@ntlworld.com> wrote:
>>>>>> All number formats are set to 2 decimal places.
>>>>>> I'm finding that percentage calculations are rounding
>>>>>> up to 2 decimal places but when the result in currency
>>>>>> is subtracted from another figure the sum is rounded
>>>>>> down.
>>>>>
>>>>> I'm sure that is only by coincidence.
>>>>>
>>>>> Formatting to 2 decimal places always results in rounding.  But note 
>>>>> that rounding can go in either direction, depending on the result. 
>>>>> For example, 1.4949 will round down to 1.49, whereas 1.495 will round 
>>>>> up to 1.50.
>>>>>
>>>>> Moreover, rounding due to formatting per se only changes the 
>>>>> appearance of the value.  It does not change the value itself.  So, 
>>>>> continuing with my example of 1.4949 in A1, which appears as 1.49, the 
>>>>> formula =2*A1 will appear to result in 2.99, not 2.98 as you might 
>>>>> incorrectly expect. The actual value will be 1.9898.
>>>>>
>>>>>
>>>>>> This gives results such as 6 - 3 = 4.
>>>>>
>>>>> I presume you mean 6.00 - 3.00 = 4.00, since you said that numbers are 
>>>>> formatted to 2 places.
>>>>>
>>>>> Try formatting all the cells to many decimal places; you might need to 
>>>>> display as many as 15 significant digits.  That might explain the 
>>>>> problem in perception.
>>>>>
>>>>> In any case, the solution is to explicitly round the value itself, not 
>>>>> just the formatted number.  There are two ways to do that:  (a) use 
>>>>> the ROUND function around your formulas, e.g. =ROUND(formula,2); and 
>>>>> (b) set the "Precision as displayed" option under Tools > Options > 
>>>>> Calculation (in Excel 2003).  But I deprecate the use of PAD (#b) for 
>>>>> a variety of reasons.
>>>>>
>>>>> One reason not to use PAD is your very situation:  you might want the 
>>>>> percentage value to be accurate to as many decimal places as possible, 
>>>>> which will vary with the magnitude of the percentage, whereas you 
>>>>> might want the product of the percentage times a dollar amount to be 
>>>>> rounded to the penny.
>>>>>
>>>>> So you want to use ROUND selectively.  You would not round the 
>>>>> expression that the percentage is derived from. You would probably 
>>>>> round all expressions that represent dollars and cents.
>>>>>
>>>>> If you still have questions, if would be help if provide some details, 
>>>>> such as the formulas involved and the "exact" cell values to 15 
>>>>> significant digits.
>>>>>
>>>>>
>>>>> ----- original message -----
>>>>>
>>>>> "Carrie" <pantscarolyn.smith2@ntlworld.com> wrote in message 
>>>>> news:2D765BAA-4D9F-40B2-B7DC-114FCCB793FF@microsoft.com...
>>>>>> I'm using Excel 2007 but 'save as' 97-2003 as colleagues have 
>>>>>> different versions.
>>>>>> All number formats are set to 2 decimal places.
>>>>>>
>>>>>> I'm finding that percentage calculations are rounding up to 2 decimal 
>>>>>> places but when the result in currency is subtracted from another 
>>>>>> figure the sum is rounded down.
>>>>>>
>>>>>> This gives results such as 6 - 3 = 4.
>>>>>>
>>>>>> I'm sure there must be a simple answer but I can't find it.  Hoping 
>>>>>> someone here knows, as checking all simple calcuations is getting 
>>>>>> ridiculous !
>>>>>>
>>>>>> Carrie
>>>>>>
>>>>>
>>>>
> 

0
Joe
2/14/2010 4:06:49 PM
Reply:

Similar Artilces:

rounding #9
is there a way to have a column round automatically instead of having to round onto another column? I don't want to add another column to just be able to round. -- denhar ------------------------------------------------------------------------ denhar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24089 View this thread: http://www.excelforum.com/showthread.php?threadid=378356 If the column contains formulas, the formulas can incorporate the Round() function within themselves. For example, if the formula is: =A1*B1 it could be revised to: =ROUND(A1*...

round() ?
Hello How can i round float to 2 digits after '.' ? Thanx Quick and Dirty: Multiply by 100, add 0.5 (to round up correctly). convert to long, then back to float, then divide by 100. This is limited to values that will fit within a long, and is not efficient. It is reliable. also: float val; val = val - fmod(val, .01); Or, the real thing: char *_fcvt( double value, int count, int *dec, int *sign ); Which is a char string, which you can then feed to atof() and get your rounded value back. Alas, beware errors in precision limits! Balboos user@domain.invalid wrote: >...

Rounding question
I'm using Excel 2007 but 'save as' 97-2003 as colleagues have different versions. All number formats are set to 2 decimal places. I'm finding that percentage calculations are rounding up to 2 decimal places but when the result in currency is subtracted from another figure the sum is rounded down. This gives results such as 6 - 3 = 4. I'm sure there must be a simple answer but I can't find it. Hoping someone here knows, as checking all simple calcuations is getting ridiculous ! Carrie "Carrie" <pantscarolyn.smith2@ntlworld.com> w...

Problem with rounding currency values
Hi, I am using the Sales Global Procedure SOP_Calculate_Trade_Discount_Split. This procedure returns the line trade discount. The line trade discount returned is rounded off. eg: if value is 6.76 then it returns 7.00. Is there some place where i can specify to turn off the rounding off? The output type is Currency. Any help is greatly appreciated. Thanks & Regards, Sup ...

Round Up and Round Down Time
Good Morning & a Happy New Year to all subscribers in this group. I want to round up Start times and round down Finish times to the next or previous 15 minutes in my spreadsheet. I have found MROUND but this does not work for me in all cases. There does not seem to be MROUNDUP or MROUNDDOWN available. I can get the results I want using a VLOOKUP table but is there another way? Regards to all, Dave Moore =FLOOR(A1,TIME(,15,)) =CEILING(A1,TIME(,15,)) -- David Biddulph DaveMoore wrote: > Good Morning & a Happy New Year to all subscribers in this group. > >...

Concatenate question
Is it possible to concatenate only the cells in a range which contain text, not numbers? (Excel 2003). -- Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me) hi Paul, with vba ? -- isabelle Le 2011-12-10 08:18, Paul Hyett a �crit : > Is it possible to concatenate only the cells in a range which contain text, not numbers? > > (Excel 2003). On Sat, 10 Dec 2011 at 08:25:26, isabelle <i@v.org> wrote in microsoft.public.excel : >hi Paul, > >with vba ? Preferably not, as I don't know how to use that. -- Paul Hyett, Chel...

MSVC++ app type choice, form design questions
If anyone has a few minutes to spare, I would appreciate hearing some advice regarding MSVC++ (and possibly an alternative development environment). For the first time in a couple of years I'm back to standalone application development. I've used C, assembler, and xxxBasic extensively in the past (including some QBasic/ASM stuff best forgotten <grin>), and I used MSAccess VBA and MSVC(5?) a few years back, so my first instinct was to pick up a copy of the latest-n-greatest MS C++ product, MSVC++.NET.2K3. I've noticed... er, "a few changes", but I built a few s...

Automatic Rounding problems
I am using Money 99 and, only on investments section, when I try enter a price or quantity with a decimal part, it simply round it to an integer number automatically when I exit the edit box. Do you know how can I make it work correctly. My Contorl Panel / Currency is already configured to 2 decimal digits and it works perfectly in the other fields where I have to put a value. Thanks for your help. Fabio Wasn't Money 99 around before they decimalized stocks? May not be possible to fix it... Fabio Pires wrote: > I am using Money 99 and, only on investments section, > when...

Excel 2007 Question #4
I want to bring data into an excel 2007 worksheet that is stored in a sql database table. The idea is to parameterize the "where" clause in the sql select statement limiting the result set. There are lots of examples in the literature with regard to bringing in data stored in cubes --- but not so much from data in sql table. The basic issue is I want bring in descriptive information about say a contract (things like description, signing date, etc). Cubes which most (all) of the literature deals with are dealing with aggregations of amounts or "measures" and not so...

Complex licensing question
Does my perpetual student license for office 2002 for my windows machine carry over if i want to get office 2004 for my mac? On 10/26/05 8:27 PM, in article 1130372844.100083.145000@g44g2000cwa.googlegroups.com, "Lucas.Wear@gmail.com" <Lucas.Wear@gmail.com> wrote: > Does my perpetual student license for office 2002 for my windows > machine carry over if i want to get office 2004 for my mac? > No, but if you really conform to the requirements for a MACBU student/teacher license, you can get Office Mac for that price. -- Bob Greenblatt [MVP], Macintosh bobgreenbla...

Rounding numbers in charts
HHi U'm not very good with computers. And I did a spreadsheet on excel and had percentages I wanted to put in a chart...when I created the chart (piechart) the values came out all inacurate. I wanted one to be 8% and it came out 6%. I heard you couold try and fix it using decimal places but that only makes a minor difference. Is there something I dont know about thats fairly simple to do? Hi Andrea If you'ld like to give us an example of your data (e.g. three categories and the associated percentages) and explain to us where the percentages came from - did you type th...

Query Question...
I have 2 tables...Job and Material. I am trying to configure dlookup to return value of 'Note_Text' in table 'Material' based on 'Part_Number' in table Job. DLookUp("[Note_Text]","Material","[Material] = [Job]![Part_Number]") Any help would be greatly appreciated. Thanks David Concatenate the value into the criteria string: =DLookUp("[Note_Text]","Material","[Material] = " & Nz([Job]![Part_Number],0)) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users ...

question about IMF
Dear all: If I send the email out, will IMF learn the reception user and add them to white-list? If yes, where is the IMF database? Many thanks for your reply! IMF will not learn, but Outlook will allow you to automatically add people you send email to to your safe senders list. The safe senders list is stored with your mailbox on the server and IMF will respect it (as long as IMF didn't block it at the gateway). -GT "David" <david@idle.com.tw.nospam> wrote in message news:eFPi8qSyEHA.1524@TK2MSFTNGP09.phx.gbl... > Dear all: > > > > If ...

Question about E-mail templates in 3.0
Do anyone knows whether it is possible to customize email templates to include e.g. flash or jpeg’s or just a plain word dodument?? ...

Outlook Question #7
Gang, I have a client that I am working with and the strangest thing happened! I was working on a domain authentication issue and had to delete a user account from a computer. After this I recreate the user and no email, so strange! All of my client's emails are gone all of his personal saved emails everything, In fact I have to reconfigure the Outlook mail settings just to get email running. So the end result......I cant find the guys email and I have no idea what is going on! HELP!! Client OS XP Outlook 2002 patched Thanks Joe Hi, Even if you create a new user with...

managed newsgroup questions
We have a support plan with microsoft, this is supposed to allow us to take part in the managed newsgroups. I have a few questions regarding this: In customersource under managed newsgroup maintenance, I have my email address set to be monitored and entered. On the discussion forum under my profile, the email address is set to the same address as in customersource except it has .(donotspam) on the end. Is this alright or do they need to match exactly? If so which one should be changed? Do I have to post questions in a special place other than the public discussion board if I want m...

Windows 7/64 question
Can someone tell me why my Win 7/64 seems to have a single default folder where almost everything is saved, unless I am careful and force a change? I'm talking about just one folder, everything seems to be forced to go there on saving. The folder is ...Users\my-user-name\documents\excel files This is a pain in the a** for me. If I haven't specified a certain save location for a software, the saved files go to me excel files folder. This isn't just ms office, it happens with almost all software I have installed. I removed everything from Libraries\documents ...

Question...
Ok I have a list of dates spread across the top of my spreadsheet liste for every Monday from now until the end of December. And every week come into the document and highlight the week that it is currently i green, and turn the last week's back to white. Is there a way to d this automatically so that I do not have to manually do it every week? Thanks, Tyeja -- Message posted from http://www.ExcelForum.com Hi try using a conditional format.e.g. if you data starts in A1 try: - select your data in row 1 - goto the conditional format dialog and enter the following formula: =(a1<TODA...

simple format question
i have the following entered as the format for a text box: (000) 000-0000" x"000 but am getting a display that looks like this: (000) 212-6757 x760 when it should be: (212) 675-7760 The field has number data type and the last 3 digits (the phone number extension) is not always included (I suspect that i may need to break the extension out to a seperate field (?) any thoughts would be greatly appreciated -mark Mark Kubicki wrote: >i have the following entered as the format for a text box: >(000) 000-0000" x"000 > >but am ...

Security Question/Issue
When I push send/receive manually, the accounts detail displays that I am sending and receiving a total of 5/7 times and then jumps back to 4/4 which is what it should be. Is there any reason that Outlook would need to do it 5/7 times? Thanks.. Submitted using http://www.outlookforums.com "Jim" <jim[dot]mcevoy[at]bull[dot]com> wrote in message news:u%23tKo5V5KHA.3184@TK2MSFTNGP05.phx.gbl... > When I push send/receive manually, the accounts detail displays that I am > sending and receiving a > total of 5/7 times and then jumps back to 4/4 which is what i...

How to round & sum only the displayed (rounded) numbers
I have rounded some numbers to the thousands digits. I need help finding a formula that rounds the displayed (rounded thousands) digits. Everything I try only calculates it based on the unrounded numbers and as such many sum numbers are off by 1 (or really 1,000). Any help would be awesome! thanks On Fri, 19 Feb 2010 06:57:01 -0800, ALG <ALG@discussions.microsoft.com> wrote: >I have rounded some numbers to the thousands digits. I need help finding a >formula that rounds the displayed (rounded thousands) digits. Everything I >try only calculates it based on t...

emails lost with compression error. Restore question.
I run Vista Home Premium and have Windows Home and Student. I use Windows mail. During a mail compression I got an error saying that the program was in use and an error had occurred. All of the e-mails were lost but there was some kind of automatic e-mail restoration. I was able to retreive what I needed from a restore e-mail folder. My question -- There are now a bunch of files in my trash bin relating to the restored e-mails. They showed up there by themselves. When I click on them they are listed as restored Windows Mail files and Recovered File Fragments. Since I have...

2 questions about CreateProcess(...)
Hi, How would I start an application hidden or minimized? PROCESS_INFORMATION pi; STARTUPINFO si; memset( &pi, 0, sizeof(PROCESS_INFORMATION) ); memset(&si, 0, sizeof(STARTUPINFO)); si.cb= sizeof(STARTUPINFO); si.dwFlags = STARTF_USESHOWWINDOW; si.wShowWindow = SW_SHOW; si.lpDesktop = ""; CreateProcess( NULL, (char*)"C:\\WinNT\\System32\\Calc.exe", NULL, NULL, FALSE, NORMAL_PRIORITY_CLASS, NULL, NULL, &si, &pi ); If I add "si.wShowWindow = SW_SHOW;", the application doe...

Question -Problem
Has anybody ever seen an error message (0x800cccF6) message? I cannot configure my Outlook to retrieve Hotmail. Works with other profiles on this computer running winxp. Stan-The-Man-2006 wrote: > Has anybody ever seen an error message (0x800cccF6) message? I cannot > configure my Outlook to retrieve Hotmail. Works with other profiles > on this computer running winxp. If you have a free Hotmail account, note that it won't work anymore (even your other profiles will eventually have problems) - see http://www.slipstick.com/addins/services/online.htm#hotmail ...

question about using the offset function
Is there a way to use the offset function (or another function) as a way to supply an expanding range to other functions like 'average'? For my purposes, there may be multiple series of numbers within say a column, with each 'series' (i.e. set of contiguous rows) separated by a few blank cells. The objective of the function would be to supply a limited set of numbers (which can expand) to the function whose result is the main interest. For example, within column D, I might have rows 5-10 representing adult male fish weights, rows 15-20 representing adult female fish weights ...