Forum Discussion
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 |
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.
2 Replies
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 🙂
