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
Feb 08 2018 08:36 PM - edited Feb 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 |
Feb 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))
Feb 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
Feb 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)
Feb 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))
Feb 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
Feb 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.
Feb 12 2018 08:41 AM
hmm..Thank you Willy for the support
its solved my issue
Thank you!
Sep 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
Sep 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
Oct 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 |
Oct 29 2018 09:25 PM
Thanks for trying, I couldn't get that formula to work at all.
:(
Nov 07 2018 10:10 PM
Nov 07 2018 11:21 PM
I have 2016, I don't know what isFormula function is.
Nov 07 2018 11:57 PM
Reference : IsFormula Function
Oct 23 2019 08:16 AM
Nov 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
Feb 10 2020 03:36 AM
($1:$1=0)
i didnt't understand this thing could you please explain it.@Willy Lau
Jan 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.
Feb 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))