If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Addition problem, number always 2 cents off...Help
I have a little problem with my SUMs
I have 200 to 300 rows of A * B = C Then I tell it to do a =sum(d1:d300) All the cells are formated to currency, two decimal places. when I add up the numbers with an adding machine I'm always 2 - 22 cents off. (2 cents on this sheet, 22 cents on another sheet....) Can anyone help Thank you WTG |
#2
|
|||
|
|||
The issue is very likely to be rounding - that the product of each
multiplication has fractional cents that Excel considers in the SUM() function, even though you don't see them fractional cents on screen You can either modify your multiplication formula to be =ROUND(A*B,2) and then sum the rounded values, or you can use an array SUM() formula =SUM(ROUND(range of values,2)) You need to commit this formula by pressing CTRL-SHIFT-ENTER, rather than just Enter Duke "WTG" wrote: I have a little problem with my SUMs I have 200 to 300 rows of A * B = C Then I tell it to do a =sum(d1:d300) All the cells are formated to currency, two decimal places. when I add up the numbers with an adding machine I'm always 2 - 22 cents off. (2 cents on this sheet, 22 cents on another sheet....) Can anyone help Thank you WTG |
#3
|
|||
|
|||
A third option is Tools|Options|Calculation and check "Precision as
displayed". Note however that it would impact all calculations, not just the specific one the OP asked about. Jerry Duke Carey wrote: The issue is very likely to be rounding - that the product of each multiplication has fractional cents that Excel considers in the SUM() function, even though you don't see them fractional cents on screen You can either modify your multiplication formula to be =ROUND(A*B,2) and then sum the rounded values, or you can use an array SUM() formula =SUM(ROUND(range of values,2)) You need to commit this formula by pressing CTRL-SHIFT-ENTER, rather than just Enter Duke "WTG" wrote: I have a little problem with my SUMs I have 200 to 300 rows of A * B = C Then I tell it to do a =sum(d1:d300) All the cells are formated to currency, two decimal places. when I add up the numbers with an adding machine I'm always 2 - 22 cents off. (2 cents on this sheet, 22 cents on another sheet....) Can anyone help Thank you WTG |
#4
|
|||
|
|||
array SUM() formula is
=ROUND(Sum(range of values),2) "Duke Carey" wrote: The issue is very likely to be rounding - that the product of each multiplication has fractional cents that Excel considers in the SUM() function, even though you don't see them fractional cents on screen You can either modify your multiplication formula to be =ROUND(A*B,2) and then sum the rounded values, or you can use an array SUM() formula =SUM(ROUND(range of values,2)) You need to commit this formula by pressing CTRL-SHIFT-ENTER, rather than just Enter Duke "WTG" wrote: I have a little problem with my SUMs I have 200 to 300 rows of A * B = C Then I tell it to do a =sum(d1:d300) All the cells are formated to currency, two decimal places. when I add up the numbers with an adding machine I'm always 2 - 22 cents off. (2 cents on this sheet, 22 cents on another sheet....) Can anyone help Thank you WTG |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
GET.CELL | Biff | Worksheet Functions | 2 | November 24th, 2004 07:16 PM |
Field in Table | LMB | New Users | 14 | October 11th, 2004 07:13 AM |
Format problem : "Text" & Number; Number -> Standard format. How? | BerHav | Setting Up & Running Reports | 0 | August 19th, 2004 06:31 PM |
Restart Autonumber | Traci | New Users | 22 | July 21st, 2004 01:10 AM |
Week number problem | Mike Collard | Running & Setting Up Queries | 2 | June 2nd, 2004 07:38 PM |