Split QTY equally by number of cells(variable) and also round off if uneven.

[ziggyfo]

Hi All,

I am after a formula that could help me get the following;

1. Cell "B1" is the source and will be a variable value. This value should be divided by the amount of order numbers and then split equally between the amount of orders (also variable)
2. Cell "C5:C14" must contain an equal amount
3. Cell "C15" must total the same amount as "B1" so I suspect some rounding would be required (see example scenario on the right of the image in "G15"

I don't always know how many orders there will be, but I always need to split the value in B1 equally between all orders and round it up at the bottom total to ensure its an even number and not 6.99 as below (this may occur if the value that's being divided doesn't quite total up to the same due to the decimals)

A:C and E:G are the same thing, I just gave two different examples of how the data might change and look.

Appreciate any help

 

[JimM]

In C5

=if(A5>0,$B$1/COUNT($A$5:$A$14),"")

and copy down

In C15

=ROUND(SUM(C5:C14),0)

 

[ziggyfo]

Thanks JimM, that did it.