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))
If with dynamic arrays
that could be
=LET(n,5,k,SEQUENCE(,n), maxv,3, v, MIN(ROUND(A1/n,0),maxv), rest, A1-v*(n-1), IF(k<n,v,rest))but perhaps we understood the requirement differently:
SergeiBaklan I tried the let formula but i dont seem to be able to use it. My requirement is like that but I ant to split the numbers to pieces of 8. (because for me, i need to split total manhours to 8 man hours / day/column)
Do you mean something like this?
=LET(n,5,k,SEQUENCE(,n), hrs,8, v, A1-hrs*(k-1), rest, v*(v>0), IF(k<n,IF(rest>hrs,hrs,rest),rest))Good Morning,
I am trying to do a similar thing with this formula with a bit of a difference with how I require the result to be generated. For example 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. See below representation:
*Note: is there a formula to leave a blank in remaining cells as opposed to zero?
Units Capacity Day 1 Day 2 Day 3 Day 4 Day 5 Day 6 Day 7 Day 8 Day 9 10 2 2 2 2 2 2 10 3 3 3 3 1 10 4 4 4 2 20 3 3 3 3 3 3 3 2 In D2:
=IF(MIN($B2,$A2-SUM($C2:C2))=0,"",MIN($B2,$A2-SUM($C2:C2)))
Fill down, then to the right (or vice versa)
SergeiBaklan Willy Lau JMB17 I have a similar issue I am trying to solve that goes alongside the original question.
I work for a lighting company, and sometimes we get requests for extremely long runs that need to be cut into multiple cuts (the number of which will differ based on how long the requested length is).
I have already come up with other formulas to automatically calculate the total number of cuts (which will vary) that are needed based on the request (the variable in this case), but I would like for it to automatically calculate the length of EACH individual cut automatically (which will take multiple cells, the amount of which will vary).
So for instance, lets say the total length is 333, and the number of cuts ends up being 6. Since 333/6=55.5, I need excel to list out -- | 56 | 56 | 56 | 55 | 55 | 55 | -- automatically. Listing them vertically will actually look better though. However, if the requested total length is 127, and the number of cuts ends up as 2, then it should say -- | 64 | 63 | -- and the rest of the column blank. So there are two variables that the formula needs to reference in other cells on the sheet (total length, and the total number of cuts), and the results must be in whole numbers.
I hope that's a clear description of my problem. Is there any way to accomplish this feat? It will be an incredible help to our department to have this automatically (and accurately) done for us in. Thanks in advance for any and all help.
I'll give it a try - see attached. I included some detail to give you an idea of how I'm approaching the problem (a table that shows up to 100 cuts). You don't need to keep this table, but I thought I would include it.
Also, there are several places where I'm using Row(Indirect(...)), which could be shortened if you have the sequence and/or let functions. I included what I think the formula would be in that case (untested as I don't have those functions on this machine).
