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

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Rounding a number to a multiple quantity that adds to a fixed total number (https://www.excelbanter.com/excel-worksheet-functions/6022-rounding-number-multiple-quantity-adds-fixed-total-number.html)

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

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 be
multiples of 4.

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

Do I make any sense? :(

ACK!
Joe

--
wjlo
------------------------------------------------------------------------
wjlo's Profile: http://www.excelforum.com/member.php...o&userid=16264
View this thread: http://www.excelforum.com/showthread...hreadid=276657

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

In article ,
wjlo wrote:

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 be
multiples of 4.

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

Do I make any sense? :(