Divide a whole number by a varying quantity across multiple cells

[DianneTS]

Good Afternoon,

I'm looking for a formula that will allow me to divide a whole number by another cell with a quantity that can be varied and for the results to be displayed in multiple cells also constrained by a start date. I would like to not get negative values after the original quantity has been spread accordingly. See below for an example of the result I am looking for:

Units	Capacity	Start Date	Finish Date	07/02/2022	14/02/2022	21/02/2022	28/02/2022	07/03/2022	14/03/2022	21/03/2022	28/03/2022	04/04/2022	11/04/2022
10	3	07-Feb	date after last allocation	3	3	3	1
10	4	28-Feb						4	4	2
10	2	14-Feb				2	2	2	2	2
20	3	21-Feb					3	3	3	3	3	3	2

[#2 · Accepted solution]

Hi @DianneTS 

this is a tricky one...

In my solution, you would need two different formulas:

One for the first column (in my example column E) and one for all the other columns.

E2: =IF(E$1<=$C2;"";$B2)

F2: =IF(SUM($E2:E2)=$A2;"";IF(F$1<=$C2;"";IF(AND($A2-SUM($E2:E2)<=$A2;$A2-SUM($E2:E2)>=$B2);$B2;IF($A2-SUM($E2:E2)<$A2;MOD($A2;$B2);""))))

You might need to replace the ; with , depending on your regional settings.

[#3]

Wow! Thank you. This seems to be working! I will have to test it further across my spreadsheet but from what I've looked at thus far, it does the job wonderfully! Thank you again 🙂