Forum Discussion
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...
=MIN(IF(COLUMN()=3,$B2,$B2-SUM($C2:OFFSET(C2,0,-1))), ROUNDUP($B2/(SUMPRODUCT(NOT($1:$1=0)*1)-2),0))
Like this, for example:
The formula in B4 is
=LET(
total, B1,
unit, B2,
num_1, QUOTIENT(total, unit),
num_2, num_1+1,
evenly, ROUND(MOD(total, unit), 4)=0,
seq_1, SEQUENCE(num_1, , unit, 0),
seq_2, SEQUENCE(num_2),
rest, (total-(num_1-1)*unit)/2,
seq_3, IF(seq_2<num_1, unit, rest),
IF(evenly, seq_1, seq_3)
)See the attached sample workbook.
Hello,
Works like a charm, thank you kindly 🙂 though I had to walk around it through opening file in browser version of Excel, implanting it there and then when I opened file in excel app it looks that it does work indeed 🙂
Once again thanks for helping out, i wouldn't figure it out alone that's for sure!
All the best 🙂
Jakub
Works like a charm, thank you kindly 🙂 though I had to walk around it through opening file in browser version of Excel, implanting it there and then when I opened file in excel app it looks that it does work indeed 🙂
Once again thanks for helping out, i wouldn't figure it out alone that's for sure!
All the best 🙂
Jakub