auto sum a group of numbers

[Doug Tenney]

I use Excel to figure interest earned each year on a number of funds for our
church. I have devised a formala to calculate and divide the amount of
interest earned in a given amount of days. However, when I use the autosum
function, my total is off by one cent, being one cent too low.

Thanks for any help you might b able to provide.

6/30/2003 6/30/2003 $$ DAYS 2003 INT ROUNDED 12/31/2003
INT ON as of 12/31/03 BALANCES
THIS COL

DAYS TO USE 185
INTEREST TO DIVIDE 53.52


GENERAL FUND 189.56 189.56 37,153.7600
0.5056 0.51 190.07
MEMORIALS & SPECIAL GIFTS 11,515.55 11,515.55 2,130,376.7500
28.9884 28.99 11,544.54
MOVING FUND 1,401.49 1,401.49 259,275.6500
3.5280 3.53 1,405.02
SOUND 1,056.96 1,056.96 195,537.6000 2.6607
2.66 1,059.62


TOTAL 14,163.56 14,163.56 2,622,343.7600 35.68
35.68 14,199.24


RESERVE 6,688.16 6,688.16 1,310,879.36 17.83734642
17.84 6,706.00 Number to left of arrow is correct total and number
desired, but the numbers add to one cent too high; used auto sum to set
total


VAR & RESERVE TOTAL 20,851.72 20,851.72 3,933,223.12
53.52 53.52 20,905.24






--

========================

Doug Tenney

4872 Royalton Road SW
Lancaster, Ohio 43130-9546

(e-mail address removed)

740.681.1690

fax to 740.474.6419

 

[RWN]

Round each calculation before summing them.
Formatting does not effect the precision, only the display.
For example, using B15 and B16 having values of 53/16 and B18 having
=SUM(B15:b16);
If the format is General then the answer is displayed as 3.3125 in each
cell, with the sum (B18) being 6.625
If you change the format of the three cells to 2 decimal places then
you'll have B15 and B16 = 3.31 but the sum will show as 6.63.
Changing B15 & B16 to =round(53/16,2) (round to 2 decimal places) will
result in the values being 3.31 and the sum =6.62.

Look at the "Help" for Rounding for further functions affecting values.

HTH

 

[Mark Graesser]

Doug
You could also use "Percision as displayed" under Tools>Options>Calculate. Then the values will be rounded to the displayed precision without having to modify all of your formulas

Good Luck
Mark Graesse
(e-mail address removed)

----- RWN wrote: ----

Round each calculation before summing them
Formatting does not effect the precision, only the display
For example, using B15 and B16 having values of 53/16 and B18 havin
=SUM(B15:b16)
If the format is General then the answer is displayed as 3.3125 in eac
cell, with the sum (B18) being 6.62
If you change the format of the three cells to 2 decimal places the
you'll have B15 and B16 = 3.31 but the sum will show as 6.63
Changing B15 & B16 to =round(53/16,2) (round to 2 decimal places) wil
result in the values being 3.31 and the sum =6.62

Look at the "Help" for Rounding for further functions affecting values

HT

 

[RWN]

What Doug says is true, I never use it because it has caused me
problems, but it depends on your application.

FWIW - Quoted from XL 2k Help;
Caution When you change the precision of the calculations in a
workbook by using the displayed (formatted) values, Microsoft Excel
permanently changes any constant values on the worksheets in the
workbook. If you later choose to calculate with full precision, the
original underlying values cannot be restored.


--
Regards;
Rob
------------------------------------------------------------------------

Doug,
You could also use "Percision as displayed" under

Tools>Options>Calculate. Then the values will be rounded to the
displayed precision without having to modify all of your formulas.