Rounding a number to a multiple quantity that adds to a fixed total number

[wjlo]

Sounds crazy?

Here is the data

Original Qty=175
Multiple=4
Total Rounded Qty Number=100

Original Rounded
Qty Qty
25 ?
50 ?
25 ?
75 ?
Total 175 100

I need to distribute the original quantity of 175 to add to 100 but b
multiples of 4.

I can distribute the 175 to make it add to 100 and round based on
multiple but I dont know how to allocate the remainder to one of th
numbers.

Do I make any sense?

ACK!
Jo

 

[JE McGimpsey]

One way:

A B
1 Original Qty Rounded Qty
2 25 =ROUND(A2/A$6*B$6/4,0)*4
3 50 =ROUND(A3/A$6*B$6/4,0)*4
4 25 =ROUND(A4/A$6*B$6/4,0)*4
5 75 =B$6-SUM(B2:B4)
6 =SUM(A2:A5) 100