Question description:
This user has given permission to use the problem statement for this
blog.
I have set of data and want to make to calculate weights of each data point, however if data point exceeds 10% threshold I want it to be taken at 10%. My problem is that this would affect other data points weight calculation. So in the end I want to have formula which would account for this adjustment and total weights of all data points would add up to 100%, with some of the data points being manually capped at 10%
Solved by F. A. in 51 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
04/02/2018 - 04:21
hi
Excelchat Expert
04/02/2018 - 04:21
welcome at got it pro
Excelchat Expert
04/02/2018 - 04:22
hello
User
04/02/2018 - 04:22
hi
Excelchat Expert
04/02/2018 - 04:23
in which cell do you need formula?
User
04/02/2018 - 04:24
This is just to ilustrate my point
User
04/02/2018 - 04:24
I need this formula to balance out weights in cells B15:B24
Excelchat Expert
04/02/2018 - 04:26
you need the average of B15:B24 in cell B26?
User
04/02/2018 - 04:27
No I need to come up with a calculation which would automatically rebalance the weights it cells B15:B24 so that total would be equal to 100%
User
04/02/2018 - 04:27
given I want none of the weights exceeding 10%
Excelchat Expert
04/02/2018 - 04:28
ok we have 10 value and the total of that 10 value should be 100% ? ok?
User
04/02/2018 - 04:29
yes, but individual weights have to be capped at 10%
Excelchat Expert
04/02/2018 - 04:29
the total should be <= 100% ? ok?
User
04/02/2018 - 04:30
=100%
Excelchat Expert
04/02/2018 - 04:30
but in that case all 10 cell have value 10%
Excelchat Expert
04/02/2018 - 04:31
because we have 10 cell and value of each cell is maximum 10 allowed
Excelchat Expert
04/02/2018 - 04:32
if any cell have value less than 10 then our total value can not able to make 100%
Excelchat Expert
04/02/2018 - 04:32
are you get my point?
User
04/02/2018 - 04:32
yes
User
04/02/2018 - 04:32
lets put cap on 25%
User
04/02/2018 - 04:33
I just want to know how to do it, the principle
Excelchat Expert
04/02/2018 - 04:34
ok in that case we have to give condition in total cell if value is >100 then it should be taken as 100 max
User
04/02/2018 - 04:35
no values are fixed
User
04/02/2018 - 04:35
only adjustment should be through weights calculation
Excelchat Expert
04/02/2018 - 04:35
we have to take max value as the cap value
Excelchat Expert
04/02/2018 - 04:35
like our max weight is 600
User
04/02/2018 - 04:35
ok show me
Excelchat Expert
04/02/2018 - 04:38
please check the cell D3
Excelchat Expert
04/02/2018 - 04:38
and C3
Excelchat Expert
04/02/2018 - 04:38
then check the B15:B24
Excelchat Expert
04/02/2018 - 04:39
i entered the variable formula
User
04/02/2018 - 04:39
Yes, but cell B24 is 61.54% while I dont want any individual weight exceed 25%
Excelchat Expert
04/02/2018 - 04:39
we have to consider the individual weight and take the max weight as the cap weight
Excelchat Expert
04/02/2018 - 04:40
therwise we can not fulfil the requirement the total should be 100%
Excelchat Expert
04/02/2018 - 04:41
then we have to change the value of B2:B11 until the cell D3 is lessthan or equal to 25
Excelchat Expert
04/02/2018 - 04:41
can i able to change the value of B2:B11 ?
Excelchat Expert
04/02/2018 - 04:42
hello are you there?
User
04/02/2018 - 04:42
yes
Excelchat Expert
04/02/2018 - 04:43
can i able to change the value of B2:B11?
User
04/02/2018 - 04:43
yes, but my point is that I need it to be done by a formula
Excelchat Expert
04/02/2018 - 04:44
then please change the value of B11 cell until the D3 cell is <=25%
Excelchat Expert
04/02/2018 - 04:45
Cell D3 must be a <=25 then your condition is satisfy by formula
Excelchat Expert
04/02/2018 - 04:46
now you can check the all value
User
04/02/2018 - 04:46
yes, but i understand, but I need this done by the formula in weights calculation and not by changing original data manually
Excelchat Expert
04/02/2018 - 04:46
ok you mean the value of B2:B11 should be fill by formula?
User
04/02/2018 - 04:47
No B2:B10 is original data which cannot be changed
Excelchat Expert
04/02/2018 - 04:47
then which data should be filled by formula?
User
04/02/2018 - 04:48
b15:b24
Excelchat Expert
04/02/2018 - 04:48
yes it is already filled by the formula
Excelchat Expert
04/02/2018 - 04:48
you can check the formula
Excelchat Expert
04/02/2018 - 04:48
=if(B10/$B$13>$D$3,0.25,B10/$B$13)
User
04/02/2018 - 04:48
FORMULA DOESNT WORK
User
04/02/2018 - 04:48
lets change parameters
Excelchat Expert
04/02/2018 - 04:49
cell D3 is not more than 25%
Excelchat Expert
04/02/2018 - 04:50
because your cap value is 25%
User
04/02/2018 - 04:50
Right and if so is there any way to make this formula. ie. if cap value is above 25%
Excelchat Expert
04/02/2018 - 04:51
then we have to change the value from B2:B11
User
04/02/2018 - 04:51
you cannot change that
User
04/02/2018 - 04:51
is there any other way to make formula ?
Excelchat Expert
04/02/2018 - 04:52
then onother way is take D3 as the Cap value
User
04/02/2018 - 04:52
ok, show me
Excelchat Expert
04/02/2018 - 04:54
please check i made it
User
04/02/2018 - 04:54
weight is still above 25%
Excelchat Expert
04/02/2018 - 04:55
in this case our cap value is = cell D3
Excelchat Expert
04/02/2018 - 04:55
46.01%
Excelchat Expert
04/02/2018 - 04:55
mathematically it is not possible
Excelchat Expert
04/02/2018 - 04:56
what you say
Excelchat Expert
04/02/2018 - 04:56
if we fix the max value is 25 then if any value is >25 then it create the deficiency to make 100
Excelchat Expert
04/02/2018 - 04:57
do you understand my point?
User
04/02/2018 - 04:57
yes
User
04/02/2018 - 04:57
it is possible to do it in series, ie. rebalancing step by step
Excelchat Expert
04/02/2018 - 04:58
yes in that case we have to rebalance either the maximum value by reducing or the minimum value increasing
User
04/02/2018 - 04:58
yes
Excelchat Expert
04/02/2018 - 04:59
now you can adjust the D3 is <=25%
Excelchat Expert
04/02/2018 - 04:59
by changing the value of B2:B11
User
04/02/2018 - 05:00
yes, I know how to do it manually
Excelchat Expert
04/02/2018 - 05:00
are you get the point?
User
04/02/2018 - 05:00
I need to do it in formula
User
04/02/2018 - 05:00
automatically
User
04/02/2018 - 05:00
in B15:B24
Excelchat Expert
04/02/2018 - 05:01
you have B3 is 46% ok
Excelchat Expert
04/02/2018 - 05:01
and you reduce it to 25% ok?
User
04/02/2018 - 05:01
no B2:B11 cannot be changed
Excelchat Expert
04/02/2018 - 05:02
then you have the total deficit is 21%
Excelchat Expert
04/02/2018 - 05:02
in cell B26 must be the 100-21 = 79%
Excelchat Expert
04/02/2018 - 05:03
your total % is never be the 100%
Excelchat Expert
04/02/2018 - 05:03
in that case
Excelchat Expert
04/02/2018 - 05:04
it is a simple logic
Excelchat Expert
04/02/2018 - 05:04
do you get my point?
Excelchat Expert
04/02/2018 - 05:06
hello
User
04/02/2018 - 05:07
y
Excelchat Expert
04/02/2018 - 05:08
always is it a deficit from 100% if we reduce any value to 25%
User
04/02/2018 - 05:09
ok so we need to redistribute
Excelchat Expert
04/02/2018 - 05:09
yes
Excelchat Expert
04/02/2018 - 05:09
maximum value is not greaterthan the 25%
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
