Divide a whole number by a varying quantity across multiple cells

Copper Contributor

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:


UnitsCapacityStart DateFinish Date07/02/202214/02/202221/02/202228/02/202207/03/202214/03/202221/03/202228/03/202204/04/202211/04/2022
10307-Febdate after last allocation 3331      
10428-Feb     442    
10214-Feb   22222    
20321-Feb    3333332 
2 Replies
best response confirmed by VI_Migration (Silver Contributor)

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.

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 :)