Forum Discussion

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

Formulas and Functions

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

Silver Contributor

Feb 09, 2018

Remya,

your cell references (A2, B2, ...) can't be correct.

I assume "Category" is in A1 and "2" is in F2.

=IF(COLUMN()=6,$B2-SUM($C2:$E2),ROUNDUP($B2/4,0))

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
  Iron 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
    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!

Resources