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

"Susan Lammi" wrote...
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

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.

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.

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

In article . net,
says...
Sort of
The % values will always total 100%
The values in D2:D373 are whole numbers.
the values I will place in cols E, F, G and H need to be whole numbers
E2+F2+G2+H2 must = D
rounding issues are causing me problems
For this example
D2=21
A1=.1
A2=.2
A3=.3
A4=.4
results in 2.100 4.200 6.300 8.400

rounded to
2.00 4.00 6.00 8.00


leaves a difference of 1

I think I see some light in your answer can you expand based on the above



"Harlan Grove" wrote in message
...
"Susan Lammi" wrote...
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

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.

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.