Rounding parts of a number while maintaining the Total

  • Thread starter Thread starter Susan Lammi
  • Start date Start date
S

Susan Lammi

I have a column of numbers which need to be split into between 1-4 new
columns based on percentage values stored in my worksheet

if rngPercents = A1:A4
and rngNumbers=D2:D373

I need
Col E=rngPercents(1,1)*rngNumbers
Col F=rngPercents(2,1)*rngNumbers
Col G=rngPercents(3,1)*rngNumbers
Col H=rngPercents(4,1)*rngNumbers

Now the problem

I need the values in Cols E-H to be whole numbers which will still total
the Original Numbers in Col D
This involves rounding and for the life of me I can't come up with a method
that will ensure the correct totals

I am willing to use VBA or formulas to accomplish this

Some Math Wizard must have done this before and your help will be greatly
appreciated.

Thanks
 
I have a column of numbers which need to be split into between 1-4 new
columns based on percentage values stored in my worksheet

if rngPercents = A1:A4
and rngNumbers=D2:D373

I need
Col E=rngPercents(1,1)*rngNumbers
Col F=rngPercents(2,1)*rngNumbers
Col G=rngPercents(3,1)*rngNumbers
Col H=rngPercents(4,1)*rngNumbers

Now the problem

I need the values in Cols E-H to be whole numbers which will still total
the Original Numbers in Col D
This involves rounding and for the life of me I can't come up with a method
that will ensure the correct totals
Click to expand...

So the values in D2:D373 total to a whole number? And A1:A4 total 100%? And you
want SUM(E2:H373) = SUM(D2:D373)? Net of floating point rounding error, if you
answer yes to all the questions above, SUM(E2:H373) will equal SUM(D2:D373). Are
you also rounding the values in E2:H373? If so, then search D2:D373 to find the
largest value in absolute value and A1:A4 to find the largest percentage.
Replace the 'intersecting' formula with SUM(D2:D373) less the sum of all the
other cells in E2:H373. If A3 were the largest percentage and D123 the largest
value in absolute value, replace G123 with

=SUM(D2:D373)-SUM(E2:F373,G2:G122,G124:G373,H2:H373)

This accumulates rounding error in the single largest cell. If you have
pathological rounding error totalling > 10 or so, you may need to spread the
rounding error over the N largest values in all columns in order to get the
average correction, total rounding error divided by N, down to an acceptable
amount, generally < 0.05 or 0.01. This isn't a simple process. For example, if
you had only one value, 1.00, and three percentages, all exactly 1/3, then the
rounded values would all be 0.33, totaling 0.99, and no obvious place to put the
correction term.
 
Basically, you cannot calculate all the numbers in the split using the
percentage formula. One of the numbers must be
intended total - sum(all other computed splits)

The question is which number should contain this fudge factor? One way
is to stick the fudge factor in the last number being calculated. A
more sophisticated way is to correct the largest split value because
this will reduce the percentage impact of the fudge factor.

In your case, if the split percentages are pre-determined as 0.1, 0.2,
0.3, and 0.4 and will not change, the largest split will be the last
number. So, enter

in E2 =ROUND($A$1*$D2,0)
in F2 =ROUND($A$2*$D2,0)
in G2 =ROUND($A$3*$D2,0)
in H2 =D2-SUM(E2:G2).

Copy E2:H2 to rows 3:<whatever>

A final note. As Harlan pointed out, if the correction, or fudge,
factor, given by D2-SUM(E2:G2)-ROUND($A$4*$D2,0), is large, you might
have to adopt a more sophisticated approach and assign it not to just
one element (H2 above) but to multiple elements. Hopefully, you don't
need that level of adjustment. :)

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top