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