Number Distribution in cells

[sarahslc]

I have been trying to create a matrix that has a maximum of 24 slots in each window and if it exceeds the maximum it takes
the extra slots and puts them in the next most popular window

Before distribution Saturday
Window 1 27
Window 2 22
Window 3 11
Window 4 10
Window 5 6
Window 6 4
Total 80


After distribution Saturday
Window 1 24
Window 2 24
Window 3 12
Window 4 10
Window 5 6
Window 6 4
Total 80


I have been manually changing them, however, is there a way to make excel automatically take out the slots exceeding 24 and put them in the
next most popular window until it has either filled all extra slots or reached 24 and moves onto the next most popular window?

Any help would be much appreciated.

-------------
MODERATOR'S NOTE: Please select the correct forum for posting your questions.

[windknife]

Maybe,

G2

=MIN(SUM($C$2:C2)-IF(ROWS($C$2:C2)=1,0,SUM($G1:G$2)),24)

drag down.

See attached.

HTH,
WindKnife

[sarahslc]

Thank you that was very helpful!

I’m not sure how many windows will exceed the limit or which cells will exceed it and how to distribute the excess orders across windows before and after that window depending on popularity. Would it have anything to do with ranking by popularity to distribute?


Before distribution Saturday
Window 1 6
Window 2 22
Window 3 11
Window 4 29
Window 5 6
Window 6 25
99

After distribution Saturday
Window 1 6
Window 2 24
Window 3 15
Window 4 24
Window 5 6
Window 6 24
99

[windknife]

Use three help columns to solve it.

G2

=RANK(C2,$C$2:$C$7)+COUNTIF($C$2:C2,C2)-1

H2

=SUMIF($G$2:$G$7,ROWS($G$2:G2),$C$2:$C$7)

I2

=MIN(SUM($H$2:H2)-IF(ROWS($H$2:H2)=1,0,SUM($I1:I$2)),24)

J2

=INDEX($I$2:$I$7,G2)

Drag those down. See attached.

HTH,
WindKnife