Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Footing rounded numbers
I am using the round function round(A$1*B1, 2) for B1 to B10.
sum(B1:B10)*A1 does not equal the sum of round(A$1*B1to 10, 2) It is off about $0.03. Is there a function that will round up or down to get the correct total? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Footing rounded numbers
Try:
=SUM(ROUND(B1:B10,2))*A1 Which is an array formula and must be entered with Ctrl + Shift + Enter not just Enter. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "G-man" wrote in message ... I am using the round function round(A$1*B1, 2) for B1 to B10. sum(B1:B10)*A1 does not equal the sum of round(A$1*B1to 10, 2) It is off about $0.03. Is there a function that will round up or down to get the correct total? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Footing rounded numbers
First, which is correct?
If you have, say: C1: =ROUND(A$1*B1,2) C2: =ROUND(A$1*B2,2) .... then C11: =SUM(C1:C10) will give you the "correct" sum of the results in C1:C10. If you're looking for something else, you'll need to specify how XL should determine what's correct. See http://www.mcgimpsey.com/excel/pennyoff.html In article , G-man wrote: I am using the round function round(A$1*B1, 2) for B1 to B10. sum(B1:B10)*A1 does not equal the sum of round(A$1*B1to 10, 2) It is off about $0.03. Is there a function that will round up or down to get the correct total? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Footing rounded numbers
The "correct" answer would be left side
sum(B1:B10)*A1 < sum(round(B1 to B10*A$1,2)) for example C1: =ROUND(A$1*B1,2) C2: =ROUND(A$1*B2,2) .. .. Sum (C1:C10) = sum(round(B1 to B10*A$1,2)) The rounding "error" is $.03. Is there a formula that will spread the error equally amoung all obervations (C1:C10)? "JE McGimpsey" wrote: First, which is correct? If you have, say: C1: =ROUND(A$1*B1,2) C2: =ROUND(A$1*B2,2) .... then C11: =SUM(C1:C10) will give you the "correct" sum of the results in C1:C10. If you're looking for something else, you'll need to specify how XL should determine what's correct. See http://www.mcgimpsey.com/excel/pennyoff.html In article , G-man wrote: I am using the round function round(A$1*B1, 2) for B1 to B10. sum(B1:B10)*A1 does not equal the sum of round(A$1*B1to 10, 2) It is off about $0.03. Is there a function that will round up or down to get the correct total? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Footing rounded numbers
How do you propose spreading $0.03 evenly among 10 cells (other than by
not rounding)? Which cells should get the excess? Should the spread be based on position? or should it be weighted? In article , G-man wrote: The rounding "error" is $.03. Is there a formula that will spread the error equally amoung all obervations (C1:C10)? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtracting Rounded Numbers | Excel Discussion (Misc queries) | |||
numbers being rounded | New Users to Excel | |||
how do i add rounded numbers? | Excel Discussion (Misc queries) | |||
ROUNDED NUMBERS IN FORMULA'S | Excel Discussion (Misc queries) | |||
Rounded numbers do not add properly. | Excel Worksheet Functions |