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.