SOLVED

Divide a quantity as whole number among multiple cells

Copper Contributor

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 columns, 2.75 will result in the 4 cells

But I need the excel formula to get the whole number, next integer(3 for 2.75) in all cells.

 

Any response will be helpful. Thanks

66 Replies

@Kazz84 

 

If your target number (20) is in cell A1, then try this in cell A2 and copy to cell E2:

 

=(COLUMNS($A2:A2)<5)*MEDIAN(0,3,$A$1-(COLUMNS($A2:A2)-1)*3)+(COLUMNS($A2:A2)=5)*MAX(0,$A$1-12)

 

JMB17_0-1609831107629.png

 

This works brilliantly, you are such a legend. You have changed my life in one formula. Thank you! @JMB17 

@Kazz84 

If with dynamic arrays

image.png

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:

image.png

You are welcome. I appreciate the kind words, but much of what I know came from people who are much better at this than me - I'm just the middleman.

@Willy Lau Hi Willy, how do you adjust this formula to split the total values into "pieces" of 8 instead of 2 in the example

@Sergei Baklan 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)

@JCOKER545 

Do you mean something like this?

image.png

=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))

@Sergei Baklan @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.

@CMStewart21 

 

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).

@JMB17 thanks so much for that. I really like how you made it simply show the two lengths, with the quantities of each instead of a long list of lengths. That looks a lot more clean than I envisioned.

 

Since I don't fully understand how to utilize sequences and arrays, do you think it would just be best to use the formula you created on its own worksheet, and then on the main worksheet I made, have it reference the cells with the results? I was seeing if I could adjust the formula to reference other cells on my other excel file, but never got it to work (basically because I don't have an in-depth knowledge of these types of formulas). Even though you listed a lot of info to help me, I still have gaps in my knowledge.

I'm sure either would work - put it on a separate worksheet and just link the total length and cuts or cut/paste it to your main worksheet. I should have noted that the formula in C5 would need to be confirmed with Ctrl+Shift+Enter instead of just enter after editing if you're not using office 365.

If you want to modify the formulas in B5:D10 to pick up the total lengths/cuts from wherever they are calculated, I can take a look at it if you're able to upload a copy of the workbook (as long as there's no sensitive information, or maybe just enough to get the cell references for those values and I can modify the formula).

@JMB17 Alright! I may take you up on that. But it may be a little while. I have a spreadsheet I've started, but I may need to refine it before I know where everything will be (permanently). It's kind of a semi-rough draft, and I may move things around if I decide to add or remove options. I wouldn't want you to put a lot of work in to my spreadsheet now, and then end up having to redo it. :) Thanks so much for your help by the way.

@JMB17 So I have put in some work to try and structure my spreadsheet, based on the help you and some others have given me. However, I forgot about one aspect that needs to be included in this -- which may or may not be possible?

 

If you can, take a look at the spreadsheet I've been working on. It's still rough and things will end up being moved around, but it gives you an idea of where I'm going. I also gave you notes highlighted in RED on each sheet, so you understand what each sheet is for.

 

So the issue I have is I realized that, even if we cut the entire run into the proper lengths, there is still end caps on both sides of EACH RUN - equaling about an extra 1/8" per run. So I need, somehow, to have a calculator that will calculate the lengths of each run that will be split up, but to include -- in each run -- an addition length (such as 0.125") for the end-cap or lead-wire. I would like for that to NOT be set directly in the formula, but for it to be a variable, because depending on the product, it may need a different type of lead wire which is a different length than 1/8". (I hope I'm making some sense... if not, please let me know and I will try to explain it better).

 

So if you are willing to give this a shot or point me in the right direction, that would be amazing. The last bit of this, if I can solve this issue, will be to add a drop-down list on the main page to choose between our different products -- which, when selected, will change the max run length, cut increment, and lead-wire/end-cap space needed per run. Those are really the main 3 variables in all of this.

 

Let me know if I'm asking too much, or if I'm confusing you. You've helped a ton so far. I appreciate anything you can offer me from this point. :)

@Sergei Baklan @JMB17 

 

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?

UnitsCapacity Day 1 Day 2Day 3Day 4Day 5Day 6Day 7Day 8Day 9
102 22222    
103 3331     
104 442      
203 3333332  

 

@DianneTS 

Sorry, that's old thread and I don't remember what was before. Better to start from scratch. Could you please provide small sample file with manually added desired results and indicate on which Excel you are?

@DianneTS 

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)

@Hans Vogelaar 

 

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:

 

UnitsCapacity Start Finish03/08/202104/08/202105/08/202106/08/202107/08/202108/08/202109/08/202110/08/202111/08/2021
102 03/08/202107/08/202122222    
103 03/08/202107/08/2021331      
104 05/08/202111/08/2021  442    
203 05/08/202111/08/2021  3333332

@DianneTS 

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)))))

Thank you for your expertise! Much appreciated.
I have a similar issue. I want to spread a base number over 7 cells to the right and the formula above works fine. My additional issue is that I want to repeat the process every 7 cells. Ex. 17 units to be rented each week. the above formula gives 3, 3, 3, 3, 3, 2, 0 (Mon - Sun) Then the following Mon it repeats using the same base of 17. Thanks in advance.