Forum Discussion

Remya Ramakrishnan's avatar
Remya Ramakrishnan
Copper Contributor
Feb 09, 2018
Solved

Divide a quantity as whole number among multiple cells

Hello, Any suggestion for an excel formula to derive A2, B2, C2, D2 values.   Eg: Category Income A B C D Sal1 11 3 3 3 2   if we divide 11 among 4 c...
excel
Formulas and Functions
  • Willy Lau's avatar
    Willy Lau
    Feb 09, 2018
    =MIN(IF(COLUMN()=3,$B2,$B2-SUM($C2:OFFSET(C2,0,-1))), ROUNDUP($B2/(SUMPRODUCT(NOT($1:$1=0)*1)-2),0))
Remya Ramakrishnan's avatar
Remya Ramakrishnan
Copper Contributor
Feb 09, 2018

Hello Detlef,

Sorry I didn't explain it right!

I look for auto population of values in C2,D2,E2 and F2 cells.

and if we the quantity to divide is 5,

I  need to get

C2=2

D2=2

E2=1

F2=0(blank)

 

Willy Lau's avatar
Willy Lau
Steel Contributor
Feb 09, 2018
=MIN(IF(COLUMN()=3,$B2,$B2-SUM($C2:OFFSET(C2,0,-1))), ROUNDUP($B2/(SUMPRODUCT(NOT($1:$1=0)*1)-2),0))
  • CMullan880205's avatar
    CMullan880205
    Copper Contributor
    Dec 07, 2023

    Willy Lau or others - 

     

    I’m after this exact formula however I have individual values that the first few columns can’t go over, as opposed to just being equally divided. 

    e.g.  B2 = 21

     

    C2 can’t be more than 9, C3 can’t be more than 5, C4 can then be whatever value is remaining. 

    How could the formula be tweaked to accommodate this please? 

    Worth adding - all I need is the simple formula from the initial messages in this thread. Nothing as complicated as the latter messages!

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Dec 07, 2023

      CMullan880205 

      Please make a sample workbook with the input and the desired output available via OneDrive, Google Drive or similar.

      • CMullan880205's avatar
        CMullan880205
        Copper Contributor
        Dec 08, 2023

        HansVogelaar thanks for the quick response.

        hopefully this explains it

         

        it’s simple really - I’m just rubbish at Excel!

         

        User Charging 

         

  • Rroudabush's avatar
    Rroudabush
    Copper Contributor
    Sep 15, 2021
    I have a similar issue. I want to spread a base number over 7 cells to the right and the formula above works fine. My additional issue is that I want to repeat the process every 7 cells. Ex. 17 units to be rented each week. the above formula gives 3, 3, 3, 3, 3, 2, 0 (Mon - Sun) Then the following Mon it repeats using the same base of 17. Thanks in advance.
  • JCOKER545's avatar
    JCOKER545
    Copper Contributor
    Jan 18, 2021

    Willy Lau Hi Willy, how do you adjust this formula to split the total values into "pieces" of 8 instead of 2 in the example

  • waqar_804's avatar
    waqar_804
    Copper Contributor
    Feb 10, 2020
    ($1:$1=0)

    i didnt't understand this thing could you please explain it.Willy Lau 

  • omar12035's avatar
    omar12035
    Copper Contributor
    Nov 19, 2019

    Willy Lau Hi Willy,

     

    Thanks a lot for addressing this issue.

    I have a similar requirement.

     

    I need to distribute quantities in 1 cell among 12 columns (all months of 2020 for a forecast)

     

    I tried applying your formula, but I just got the same number as in the original cell in the cell containing the formula but fails to distribute it among the rest 11 columns. Please help

  • Allain McCallum's avatar
    Allain McCallum
    Copper Contributor
    Sep 20, 2018

    I need a similar thing with a couple of differences. I need to do the same derive values across variable columns from a number entered into the first column, but i also need to be able to manually override the formula in some cells but have the other cells adjust to the variable. 

     

    Example: A1=50, B1:F1 = 10, but if I manually adjust C1 to equal 0, the other 4 cells should auto-adjust to 12.5

    The formula should be variable to be copied across 2 cells or 20.

     

    Thoughts?

     

    Allain

    allainm@gmail.com

    • Willy Lau's avatar
      Willy Lau
      Steel Contributor
      Oct 25, 2018

      I am so sorry for the late reply.  I am not sure if the following approach suits your needs.

       

      The Formula is

      =$A$1/SUMPRODUCT(--(ISFORMULA($B$1:$F$1)))

        

           ABCDEF
      150
      =TheFormula
      =TheFormula
      =TheFormula
      =TheFormula
      =TheFormula
      • Allain McCallum's avatar
        Allain McCallum
        Copper Contributor
        Oct 30, 2018

        Thanks for trying, I couldn't get that formula to work at all.

         

        :-(

  • Remya Ramakrishnan's avatar
    Remya Ramakrishnan
    Copper Contributor
    Feb 09, 2018

    Thank you for the response, I am unable to get the expected results

    When I am pasting the formula in C2, it results in circulate reference

     

    • Willy Lau's avatar
      Willy Lau
      Steel Contributor
      Feb 10, 2018

      It is because your sheet keeps my previous version formula.  You may clear C2, D2, E2 and F2.  Then, paste the latest formula to C2.

      • Remya Ramakrishnan's avatar
        Remya Ramakrishnan
        Copper Contributor
        Feb 12, 2018

        hmm..Thank you  Willy for the support

         

        its solved my issue

         

        Thank you!

         

         

         

Resources