Forum Discussion
Divide a quantity as whole number among multiple cells
=MIN(IF(COLUMN()=3,$B2,$B2-SUM($C2:OFFSET(C2,0,-1))), ROUNDUP($B2/(SUMPRODUCT(NOT($1:$1=0)*1)-2),0))
In the 2nd row, with start date 7 February, the first non-blank value is indeed on 7 February.
But in the 3rd row, with start date 28 February, the first non-blank value in your sample is on 7 March.
I don't understand the logic behind this.
- 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 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.
- hi,
if total is divisible then this is fine and we would want 10x 117.6
thanks 🙂 Yes, that should be doable. One more question:
What if the total is evenly divisible by the unit of measure? For example, if the total is 1176.0. Do you want 10 times 117.6, or 9 times 117.6 plus 2 times 117.6/2 = 58.8?
Hi, thanks for prompt response
117.6 it's unit of measure in this case.
66.1 are based only on example given (when you split 1190.6 for as many 117.6 as possible this will leave you with 132.2 thus 2x66.1). But if there'd be let's say 1200.6, it would change to 2x 71.1.
I know in theory since there's 132.2 so you could fit one more 117.6 in there, but SAP on site, for whatever reason is set up to split last one separately to 2 equal parts.Is that even doable? 🙂
kind regards
What is the 'rule' to determine the sizes 117.6 and 66.1?
hi guys 🙂 interesting stuff but i also seem to stuck with one thing.
say that A1= number from other sheet i.e. 1190.6
how to break it down so it displays in cells below for equal amounts but last 2 parts to be equal as well?
1. 117.6
2. 117.6
3. 117.6
4. 117.6
5. 117.6
6. 117.6
7. 117.6
8. 117.6
9. 117.6
10. 66.1
11. 66.1
it's just how SAP system on site is configured and need to adjust my excel file...
Any advice much appreciated 🙂
Kind regards
I am very sorry, but I don't understand at all, so I cannot help you.
- The production is order based. So there is no guarantee that it would start then. In my reality, project 1 could end up starting after project 3. Or a start date could change for a number of reasons, hence why I need the capacity and start date to be able to be amendable to account for any real life changes.
Why can't the project with start date 14 February start on 14 February? There is no other project that starts on 14 February.
