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
Oct 10 2021 04:07 AM - edited Oct 10 2021 04:35 AM
I am very impressed by your formula and it may be able to solved a problem that I faced.
I have a number (say 223 in A1) and I want to distribute it over a selected cells (say 20 rows from B1:B20) more or less evenly. The number in each cell must be a whole number and the sum of all rows must be exactly the same as A1. Is there a simple Excel formula to achieve this?
Please see my attached worksheet for details. Do note that the number of row is variable.
Thank you.
Oct 10 2021 05:16 AM
See the attached version. You can change the values in A1 and F1 to see the result change.
Oct 10 2021 06:17 AM
Wow, that’s fantastic. The formula works nicely by inputting number of rows into an additional cell.
I have an additional question. In a real life worksheet with lots of data and without a blank column to key in number of rows due to worksheet protection, is there anyway to tweet the formula such that it knows how to count a range of selected rows? You can try to imagine that after row 20, there is another number (say A21=333) and it needs to be redistributed to 15 rows (say B21 to B35). Hence, I need a flexible formula that can take care of any number of rows.
Thanks a lot for your help. It is really enlightening to learn from you.
Oct 10 2021 07:18 AM
A formula doesn't "know" which range you selected. To do what you want would probably require VBA code, but that seems overkill.
Without VBA, see the attached version. I moved the cell with the number of rows to column A.
Oct 10 2021 07:45 AM
Feb 08 2022 07:52 AM
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?
Feb 08 2022 08:04 AM
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?
Feb 08 2022 08:23 AM
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 |
Feb 08 2022 11:25 AM
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.
Feb 09 2022 01:10 AM
Feb 09 2022 02:41 AM
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.
Feb 09 2022 02:46 AM
Feb 09 2022 02:51 AM
Why can't the project with start date 14 February start on 14 February? There is no other project that starts on 14 February.
Feb 09 2022 02:54 AM
Feb 09 2022 02:59 AM
I am very sorry, but I don't understand at all, so I cannot help you.
May 24 2022 11:44 PM
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
May 25 2022 12:44 AM
What is the 'rule' to determine the sizes 117.6 and 66.1?
May 25 2022 01:13 AM
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
May 25 2022 01:37 AM
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?
May 25 2022 01:39 AM