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

Resources