SOLVED

# Divide a whole number by a varying quantity across multiple cells

Copper Contributor

# Divide a whole number by a varying quantity across multiple cells

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 Replies
best response confirmed by VI_Migration (Silver Contributor)
Solution

# Re: Divide a whole number by a varying quantity across multiple cells

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.

# Re: Divide a whole number by a varying quantity across multiple cells

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