In a column of numbers I subtotal at various points - I use the
Subtotal(9,Range)
These subtotals are rounded to nearest 100.
I then have a grand total, again using Subtotal(9,Range)
This grand total is rounded to nearest 1000

I need to ensure that sum of the subtotals = Grand total (which they often
don't due to rounding) & so I need to add in a balancing number to one of the
subtotals.
I have a cell which gives me the number I need, but I get a circular
reference error when I link to it. Is there a way in which I can ignore the
formula, but use the result?
This needs to be an auto function as the spreadsheet has other user
variables which effect the numbers

Can anybody help?
David

The basic fallacy of this goal is highlighted by the possibility that all of
the subtotals could be identical. Which would you then arbitrarily make
different to impose a condition that does not mathematically follow?

Jerry

"Saintsman" wrote:

In a column of numbers I subtotal at various points - I use the
Subtotal(9,Range)
These subtotals are rounded to nearest 100.
I then have a grand total, again using Subtotal(9,Range)
This grand total is rounded to nearest 1000

I need to ensure that sum of the subtotals = Grand total (which they often
don't due to rounding) & so I need to add in a balancing number to one of the
subtotals.
I have a cell which gives me the number I need, but I get a circular
reference error when I link to it. Is there a way in which I can ignore the
formula, but use the result?
This needs to be an auto function as the spreadsheet has other user
variables which effect the numbers

Can anybody help?
David

Hope this explains better! The document is primarily for presentation & I
want to round totals.
I add numbers below subtotal I'm afraid so subtotal 1 = 30150+75400 etc
Only subtotals and grand totals will be visible on the final document, but
they do need to add up correctly

subtotal 1 105,600 round=100
30,150
75,400
subtotal 2 109,800 round=100
7,000
73,500
19,550
9,720
subtotal 3 1,400 round=100
1,420

Grand total 217,000 round=1000
216,800 actual (sub1+sub2+sub3)
200 balancing total added to subtotal 3

How can I add the balancing figure to a subtotal (apart from doing it
manually)

I understood that from your original post. The basic problem is that (even
when rounding to the same degree everywhere) the rounded sum of numbers need
not be equal to the rounded sum of rounded numbers.

What you are trying to do distorts the data. If the consumers of your
report need to see the components, then wouldn't it be better to simply
explain that mathematical reality to them rather than giving them distorted
data?

Taking your actual example, note that the sum of all the raw numbers is
216,740, which rounds to 216,700, not 216,800 (the first distortion).
Second, if the consumers need to see the hundreds in sub1-sub3, how does it
help them to see a wrong hundreds figure in at least one of them? Moreover,
if they can tolerate a distortion in those figures why distort the smallest
value (14% distortion) instead of the largest value (0.2% distortion)?

Jerry

"Saintsman" wrote:

Hope this explains better! The document is primarily for presentation & I
want to round totals.
I add numbers below subtotal I'm afraid so subtotal 1 = 30150+75400 etc
Only subtotals and grand totals will be visible on the final document, but
they do need to add up correctly

subtotal 1 105,600 round=100
30,150
75,400
subtotal 2 109,800 round=100
7,000
73,500
19,550
9,720
subtotal 3 1,400 round=100
1,420

Grand total 217,000 round=1000
216,800 actual (sub1+sub2+sub3)
200 balancing total added to subtotal 3

How can I add the balancing figure to a subtotal (apart from doing it
manually)

Ignoring the reasons why I am trying to do this, ignoring the error issues,
data distortation etc etc
Is it actually possible to apply a balancing number without invoking the
circular reference error..ie if the difference between two number is 200, can
I then 200 to one of the numbers to make them both the same

"Jerry W. Lewis" wrote:

I understood that from your original post. The basic problem is that (even
when rounding to the same degree everywhere) the rounded sum of numbers need
not be equal to the rounded sum of rounded numbers.

What you are trying to do distorts the data. If the consumers of your
report need to see the components, then wouldn't it be better to simply
explain that mathematical reality to them rather than giving them distorted
data?

Taking your actual example, note that the sum of all the raw numbers is
216,740, which rounds to 216,700, not 216,800 (the first distortion).
Second, if the consumers need to see the hundreds in sub1-sub3, how does it
help them to see a wrong hundreds figure in at least one of them? Moreover,
if they can tolerate a distortion in those figures why distort the smallest
value (14% distortion) instead of the largest value (0.2% distortion)?

Jerry

"Saintsman" wrote:

Hope this explains better! The document is primarily for presentation & I
want to round totals.
I add numbers below subtotal I'm afraid so subtotal 1 = 30150+75400 etc
Only subtotals and grand totals will be visible on the final document, but
they do need to add up correctly

subtotal 1 105,600 round=100
30,150
75,400
subtotal 2 109,800 round=100
7,000
73,500
19,550
9,720
subtotal 3 1,400 round=100
1,420

Grand total 217,000 round=1000
216,800 actual (sub1+sub2+sub3)
200 balancing total added to subtotal 3

How can I add the balancing figure to a subtotal (apart from doing it
manually)

To do it in worksheet formulas without VBA, you would need two sets of
subtotals; one set giving the actual calculated subtotals, which could be
hidden (hide the row or column containing them); and the other set giving the
distorted subtotals.

Jerry

"Saintsman" wrote:

Ignoring the reasons why I am trying to do this, ignoring the error issues,
data distortation etc etc
Is it actually possible to apply a balancing number without invoking the
circular reference error..ie if the difference between two number is 200, can
I then 200 to one of the numbers to make them both the same

"Jerry W. Lewis" wrote:

I understood that from your original post. The basic problem is that (even
when rounding to the same degree everywhere) the rounded sum of numbers need
not be equal to the rounded sum of rounded numbers.

What you are trying to do distorts the data. If the consumers of your
report need to see the components, then wouldn't it be better to simply
explain that mathematical reality to them rather than giving them distorted
data?

Taking your actual example, note that the sum of all the raw numbers is
216,740, which rounds to 216,700, not 216,800 (the first distortion).
Second, if the consumers need to see the hundreds in sub1-sub3, how does it
help them to see a wrong hundreds figure in at least one of them? Moreover,
if they can tolerate a distortion in those figures why distort the smallest
value (14% distortion) instead of the largest value (0.2% distortion)?

Jerry

"Saintsman" wrote:

Hope this explains better! The document is primarily for presentation & I
want to round totals.
I add numbers below subtotal I'm afraid so subtotal 1 = 30150+75400 etc
Only subtotals and grand totals will be visible on the final document, but
they do need to add up correctly

subtotal 1 105,600 round=100
30,150
75,400
subtotal 2 109,800 round=100
7,000
73,500
19,550
9,720
subtotal 3 1,400 round=100
1,420

Grand total 217,000 round=1000
216,800 actual (sub1+sub2+sub3)
200 balancing total added to subtotal 3

How can I add the balancing figure to a subtotal (apart from doing it
manually)