Prorations

[gary]

What formula should I use to prorate a total amount among a
number of items so the sum of the items' amounts add up to
the total amount? (I can't have penny-differences)

 

[AlfD]

Hi!

If the numbers in proportion to which you want to share the amount ar
in B1:B10, say and the amount to be shared is in A1
then put =$A$1*B1/(SUM($B$1:$B$10)) in C1 and copy down.

There is no power on earth that will guarantee that this will neve
have a penny light or a penny too much unless
sum(B1:B10) adds up to a number which divides exactly into A1 to th
degree of accuracy you want.

Neither is formatting guaranteed to paper over the cracks.

I'm sure you have seen plenty of tables of statistics with the footnot
that the columns of percentages may not add up to exactly 100 due t
rounding errors.

I the end, you either live with the small error margin or you put
bigger one in somewhere else by manually changing something to make th
total right.

Alf

Al

 

[Guest]

If you are talking about Dollars, I would round my numbers and then multiply or divid or what ever Round(CellName), 2, think this is the right syntax. Or Take the total number you are prorating and round the totals, sum those totals less one proration and subtract it from the total to get the final proration which would make the totals add up exactly.