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))
Thank you so much for this. To add a spanner to the works, how would the formula need to change if I wanted to constrain the result between a start and finish date? For example, if the day columns were actual dates and I wanted the result to only be populated from a specified start date, see below:
| Units | Capacity | Start | Finish | 03/08/2021 | 04/08/2021 | 05/08/2021 | 06/08/2021 | 07/08/2021 | 08/08/2021 | 09/08/2021 | 10/08/2021 | 11/08/2021 | |
| 10 | 2 | 03/08/2021 | 07/08/2021 | 2 | 2 | 2 | 2 | 2 | |||||
| 10 | 3 | 03/08/2021 | 07/08/2021 | 3 | 3 | 1 | |||||||
| 10 | 4 | 05/08/2021 | 11/08/2021 | 4 | 4 | 2 | |||||||
| 20 | 3 | 05/08/2021 | 11/08/2021 | 3 | 3 | 3 | 3 | 3 | 3 | 2 |
In F2:
=IF(OR(F$1<$D2,F$1>$E2),"",IF(F$1=$D2,MIN($A2,$B2),IF(MIN($B2,$A2-SUM(E2:$F2))=0,"",MIN($B2,$A2-SUM(E2:$F2)))))
- 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.
- So each row is a 'project' consisting of a set amount of units. each project cannot start on the same day due to resources, hence the different start dates. Is there a formula to use to distribute units as per column B (Capacity) across cells from a nominated starting point i.e E2 or F2 to get the result as shown. So basically I need it to have 2 functions that are variable - capacity and start date.
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.
- Its to create a production plan. So each row is a separate job and each job has different start dates according to capacity
In the 3rd row, with Start Date 28/02/2022, why should the numbers start at 07/03/2022 instead of at 28/02/2022?
Similar question for the 4th and 5th rows.
I would like to divide a whole number across multiple cells but using a maximum value (maybe referencing another column as this number could change between each row) and the last cell to calculate the remaining number if its below the maximum. Also, if this allocation could start from a chosen date? See below representation:
*Note: is there a formula to leave a blank in remaining cells as opposed to zero?
Units Capacity Start Date Finish Date 07/02/2022 14/02/2022 21/02/2022 28/02/2022 07/03/2022 14/03/2022 21/03/2022 28/03/2022 04/04/2022 11/04/2022 10 3 07-Feb date after last allocation (J1) 3 3 3 1 10 4 28-Feb L1 4 4 2 10 2 14-Feb O1 2 2 2 2 2 20 3 21-Feb 3 3 3 3 3 3 2 Hi Dianne,
This is a long discussion, I'm not sure what you are referring to. Could you attach a sample workbook with an indication of what you want?
Hi Hans, how would this formula need to be adjusted to only confine a particular start date and let it run so that the quantity is distributed but does not go into negative values if its not constrained by an end date?
- Thank you for your expertise! Much appreciated.
