Carrie said:
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 said:
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 said:
Carrie said:
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 said:
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
I 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
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 -----
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
