Forum Discussion
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...
=MIN(IF(COLUMN()=3,$B2,$B2-SUM($C2:OFFSET(C2,0,-1))), ROUNDUP($B2/(SUMPRODUCT(NOT($1:$1=0)*1)-2),0))
=MIN(IF(COLUMN()=3,$B2,$B2-SUM($C2:OFFSET(C2,0,-1))), ROUNDUP($B2/(SUMPRODUCT(NOT($1:$1=0)*1)-2),0))
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
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)))
A B C D E F 1 50 =TheFormula
=TheFormula
=TheFormula
=TheFormula
=TheFormula
Thanks for trying, I couldn't get that formula to work at all.
:-(
- May I have your excel version? Does it have IsFormula Function?
