Excel - IF Function Problem - Expert Solution

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
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
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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.