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

 

:-(

  • Willy Lau's avatar
    Willy Lau
    Steel Contributor
    Nov 08, 2018
    May I have your excel version? Does it have IsFormula Function?
    • Allain McCallum's avatar
      Allain McCallum
      Copper Contributor
      Nov 08, 2018

      I have 2016, I don't know what isFormula function is.

Resources