Feb 08 2018 07:06 PM
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
Jan 04 2021 11:18 PM
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)
Jan 04 2021 11:34 PM
This works brilliantly, you are such a legend. You have changed my life in one formula. Thank you! @JMB17
Jan 04 2021 11:58 PM
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:
Jan 05 2021 04:30 PM
Jan 18 2021 09:50 AM
@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
Jan 21 2021 01:47 AM
@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)
Jan 21 2021 05:59 AM
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))
Jun 19 2021 09:13 PM
@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.
Jun 20 2021 12:08 AM
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).
Jun 20 2021 01:14 PM
@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.
Jun 20 2021 10:44 PM
Jun 21 2021 04:36 PM
@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.
Jul 06 2021 11:30 AM
@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. :)
Aug 02 2021 01:53 AM
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 |
Aug 02 2021 06:51 AM
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?
Aug 02 2021 07:02 AM
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)
Aug 03 2021 04:17 AM
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 |
Aug 03 2021 05:49 AM
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)))))
Aug 03 2021 06:34 AM
Sep 15 2021 09:34 AM