Forum Discussion

DianneTS's avatar
DianneTS
Copper Contributor
Feb 09, 2022
Solved

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's avatar
    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.

Martin_Weiss's avatar
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.

  • DianneTS's avatar
    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 🙂

Resources