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