Dynamic Average

[Daniel Shanahan]

Given a set of numbers that must equal 100 - e.g.

SET A = 25, 25, 25, 25

SET B = 20, 20, 20, 20, 20

SET C = 20, 15, 40, 25

I would like to add or remove a number from the set and have the set automatically calculate its value. I am able to do this for SET A and SET B. However, I'm perplexed on how to do this for a set where the values are not equal to the average.

So, my question is, how can I add or remove a number from SET C and have the set readjust itself?

SET C = 20, 15, 40, 25, n

where n = the lowest value in the set after the values have readjusted (in this case readjusted to Total = 100/5 instead of Total = 100/4).

Context: a child table has a number field. The average of those fields is 100 / child records. I would like to add a child record and I want the value for its number field to be dynamic. Moreover, I want the number fields in the other children to adjust accordingly.

I hope this explanation is clear. I think this is a basic math question, but the answer eludes me.

Thanks.

Edited November 20, 2010 by Guest
Added context.

[fseipel]

To normalize, when adding a value to set, you would multiply all members in the set by n/(n+1)=4/(4+1)=4/5= 0.8.

The new set member then becomes 100/(n+1)=100/5 = 20 in going from 4 values to 5.

Above assumes normalization so that new set member value is always equal to the average; if it is not the average, then you apply a different weighting ratio instead of n/(n+1) accordingly, e.g. if new set value is known, you solve for weighting factor, i.e. weighting factorx(a1+a2+a3+a4)+a5=100 where a5=new member, a1 thru a4 = existing members. This reduces to weighting factor = (100-a5)/(a1+a2+a3+a4) = (100-a5) / 100 since a1+a2+a3+a4 has the constraint that it initially summed to 100.

If you are omitting a value, then all remaining values, would be multiplied by 100/SUM(remaining 4 members in set) when going from 5 to 4 values. That's because the new members, combined, must 'make up' the value of the member removed.

[comment]

Mathematically, this has very little to do with average. If the sum of existing child records is 100, and you add a new record with value X, then each child record needs to scale its value V to:

V - X * V / 100

This is assuming X itself is already in the "new scale" and will not be further adjusted.

From a database point-of-view, I would question the need for modifying existing records as the result of adding a new one. Knowing the REAL context here would be helpful.

[Daniel Shanahan]

Thanks fseipel. I will have to read this a few times in order to digest.

From a database point-of-view, I would question the need for modifying existing records as the result of adding a new one. Knowing the REAL context here would be helpful.

comment, the REAL context is a table with these attributes: criteria, weight, and score. The score can be any number between 0-100. When a new criteria is added, I would like to keep the weight ratio. The score would not change, but the weight would. So, if four records all have a weight ratio of 25, I will change them to 20. The same is true if a criteria - for some reason - is deleted.

[comment]

But what is the purpose of this? I believe these results can be calculated in real time.

if four records all have a weight ratio of 25, I will change them to 20

If all records have the same weight, then the weight attribute is redundant.

[Daniel Shanahan]

If all records have the same weight, then the weight attribute is redundant.

Indeed. I used the same weights for examples. My question concerns the situation where the weights are not the same, as in SET C above.

[comment]

I am still puzzled as to what this is REALLY about. In real life, when you measure a sample and the measurement comes out as 20 units, then that's it: this value is not going to change as a result of another sample measuring 15 units.

I am not sure what you mean by "weight" here. If all you have is a set of values {20; 15; 40; 25}, then you already know all that is to be known about this set. You know that the total value is 100 and that this total is distributed within the set as {20%; 15%; 40%; 25%}.

Now, if you add a new value of 60 to the set, the total will become 160 and the distribution will be {12.5%; 9.375%; 25%; 15.625%; 37.5%}. This can all be calculated very easily from the given set {20; 15; 40; 25; 60} - without having to modify the set's values in any way.

[David Jondreau]

the REAL context is a table with these attributes: criteria, weight, and score. The score can be any number between 0-100. When a new criteria is added, I would like to keep the weight ratio. The score would not change, but the weight would.

I'm sorry, this is not the real context. This is an abstraction of the real context.

What do these numbers actually represent in the real world?

Here's why what you're trying to do is weird to me...the operation of adding the a new criteria and re-weighting is not commutative.

Take the example of

Set F: 50 50

Add 100 to that set

Now add 200 to that set

Result: 8.3 (50), 8.3 (50), 16.6 (100), 66.6 (200)

If instead you had

Set F: 50 50

Add 200 to that set

Add 100 to that set

Result = 8.3 (50), 8.3 (50), 33.3 (200), 50 (100)

That seems really odd to me. In what situation is that sort of calculation useful?

Edited November 21, 2010 by Guest

[comment]

I am guessing this may be about a teacher giving three assignments and one test, and now they need to decide how to calculate the final grade. The request seems to be to force the weights to sum up to 100, while maintaining their relative proportions.

In truth there is no need for such adjustment, since a calculation of weighted sum/average will produce the same result with unadjusted values as it will with adjusted ones.

[Daniel Shanahan]

I appreciate the interest in this topic. It seems that this thread has divided into two parts:

1. How

2. Why

Regarding the first part, I needed a formula for both adding a number to the set and removing a number from the set. For removing a number, I went with fseipel's formula:

If you are omitting a value, then all remaining values, would be multiplied by 100/SUM(remaining 4 members in set) when going from 5 to 4 values. That's because the new members, combined, must 'make up' the value of the member removed.

.

To add a number to the set, I am using the following formula:

weight * 100 / newTotalWeight

Regarding the second part, comment is correct - the purpose is for assessment. It is a way for a project manager to create criteria for a particular project or task, weight it, and score it. The weight must total 100 (i.e. the total of all criteria for a given project) but each score can be any number between 0 and 100. Keeping the total weight at 100 helps the manager see how much weight she or he has to spend.

To illustrate:

Project A

Criteria 1: Done on time

Weight: 90

Score: 100

Criteria 2: Internal documentation complete

Weight: 10

Score: 45

Project B

Criteria 1: Done on time

Weight: 50

Score: 100

Criteria 2: Internal documentation complete

Weight: 50

Score: 100

Take the example of

Set F: 50 50

Add 100 to that set

Now add 200 to that set

Result: 8.3 (50), 8.3 (50), 16.6 (100), 66.6 (200)

If instead you had

Set F: 50 50

Add 200 to that set

Add 100 to that set

Result = 8.3 (50), 8.3 (50), 33.3 (200), 50 (100)

D J, thank you for taking interest in this thread. As mentioned in my initial post, a new item added to the set is the lowest value in the set after the values have readjusted. In the case of Set F, the third value would be 33.3 (since the first two are even).

I hope that helps clarify the purpose, even if some may disagree with its usefulness or practicality. And again, I'm grateful to fseipel and comment for posting to the "How" part.

Edited November 23, 2010 by Guest

[comment]

My issue with this method is from the user's point-of-view. Let's say I have three parameters to consider:

1. Cost

2. Time

3. Quality

On a scale of 1 to 10, I assigned the following weights to these parameters:

1. Cost - 8

2. Time - 5

3. Quality - 10

These do not add up to 100 or to any other number, and the truth is that I don't care. It's the computer's (i.e. your) job to figure it out. In fact, on the next project I might decide to use a scale of 1 to 5, or 0 to 100.

Now, let's say I need to add a fourth parameter of Customer Satisfaction. The last thing I would want to happen is for my previous assessments to be "miraculously" modified to fit someone's idea as to what they "really" should be.