02-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
02-08-2018 08:36 PM - edited 02-11-2018 04:29 PM
This is what I guess you want
=ROUNDUP(IF(B2=$B2,$B2,$B2-SUM($C2:OFFSET(C2,0,-1)))/(SUMPRODUCT(NOT($1:$1=0)*1)-COLUMN(B2)),0)
Assume that you enter the formula at C2, and copy & paste the formula to the cells you need it. If later on, you want E, F, G etc., you just need to add the headers and paste the formula.
Eg:
Category | Income | A | B | C | D |
something | 6 | 2 | 2 | 1 | 1 |
another thing | 5 | 2 | 1 | 1 | 1 |
02-08-2018 09:05 PM
Remya,
your cell references (A2, B2, ...) can't be correct.
I assume "Category" is in A1 and "2" is in F2.
=IF(COLUMN()=6,$B2-SUM($C2:$E2),ROUNDUP($B2/4,0))
02-09-2018 08:29 AM
Thank you Willy for the response, Sorry if I didn't articulate my problem clearly;
The cells in which I require values are C2,D2,E2,F2
its not required to have values in all cells, if we take 5 as example:
C2 has to take 2
D2 has to take 2
E2 has to take 1
D2 can be blank
02-09-2018 08:33 AM
Hello Detlef,
Sorry I didn't explain it right!
I look for auto population of values in C2,D2,E2 and F2 cells.
and if we the quantity to divide is 5,
I need to get
C2=2
D2=2
E2=1
F2=0(blank)
02-09-2018 09:15 AM
Solution=MIN(IF(COLUMN()=3,$B2,$B2-SUM($C2:OFFSET(C2,0,-1))), ROUNDUP($B2/(SUMPRODUCT(NOT($1:$1=0)*1)-2),0))
02-09-2018 10:16 AM
Thank you for the response, I am unable to get the expected results
When I am pasting the formula in C2, it results in circulate reference
02-09-2018 04:45 PM
It is because your sheet keeps my previous version formula. You may clear C2, D2, E2 and F2. Then, paste the latest formula to C2.
02-12-2018 08:41 AM
hmm..Thank you Willy for the support
its solved my issue
Thank you!
09-20-2018 03:40 PM
I need a similar thing with a couple of differences. I need to do the same derive values across variable columns from a number entered into the first column, but i also need to be able to manually override the formula in some cells but have the other cells adjust to the variable.
Example: A1=50, B1:F1 = 10, but if I manually adjust C1 to equal 0, the other 4 cells should auto-adjust to 12.5
The formula should be variable to be copied across 2 cells or 20.
Thoughts?
Allain
allainm@gmail.com
09-20-2018 03:42 PM
I need a similar thing with a couple of differences. I need to do the same derive values across variable columns from a number entered into the first column, but i also need to be able to manually override the formula in some cells but have the other cells adjust to the variable.
Example: A1=50, B1:F1 = 10, but if I manually adjust C1 to equal 0, the other 4 cells should auto-adjust to 12.5
The formula should be variable to be copied across 2 cells or 20.
Thoughts?
Allain
allainm@gmail.com
10-25-2018 09:57 AM
I am so sorry for the late reply. I am not sure if the following approach suits your needs.
The Formula is
=$A$1/SUMPRODUCT(--(ISFORMULA($B$1:$F$1)))
A | B | C | D | E | F | |
1 | 50 | =TheFormula | =TheFormula | =TheFormula | =TheFormula | =TheFormula |
10-29-2018 09:25 PM
Thanks for trying, I couldn't get that formula to work at all.
:-(
11-07-2018 10:10 PM
11-07-2018 11:21 PM
I have 2016, I don't know what isFormula function is.
11-07-2018 11:57 PM
Reference : IsFormula Function
10-23-2019 08:16 AM
11-18-2019 10:40 PM
@Willy Lau Hi Willy,
Thanks a lot for addressing this issue.
I have a similar requirement.
I need to distribute quantities in 1 cell among 12 columns (all months of 2020 for a forecast)
I tried applying your formula, but I just got the same number as in the original cell in the cell containing the formula but fails to distribute it among the rest 11 columns. Please help
02-10-2020 03:36 AM
($1:$1=0)
i didnt't understand this thing could you please explain it.@Willy Lau
01-04-2021 10:26 PM
I've been looking all over the internet trying to find a solution to my problem and this post seems to be on the right track but does not quite get to the necessary answer I am looking for.
I need to divide a whole number into 5 columns with a maximum of 3 in each column apart from column 5 which can be the remaining number of whole numbers,
Example: The whole number is 20
Column A 3
Column B 3
Column C 3
Column D 3
Column E 8
Is there a formula that can help me achieve this?
I would be sincerely grateful for any assistance.