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...

excel

Formulas and Functions

Martin_Weiss
Feb 10, 2022
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.
Varying quantity.xlsx11 KB

Martin_Weiss

Bronze Contributor

Feb 10, 2022

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.

Varying quantity.xlsx11 KB

DianneTS

Copper Contributor

Feb 10, 2022

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 🙂

Forum Discussion

Divide a whole number by a varying quantity across multiple cells

Resources