"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.