# Weighted Values

I hope to be able to get some assistance here, I tried reading through some of the posts, however, they are far above my math abilities..so I apologize if this is a re-post or this is a basic question for this forum..

Also this is much easier to see and understand in Excel

What I am trying to do is re-calculate various values if the total of the sum does not equal the expected total..(some values be increased, others decreased, all while maintaining that any one individaul value does not go below the minimum)

Problem after applying weights, the minimum is not maintained, thus, the desired value is not reached.

DATA:

Expected Total = 18,000

Minimum Value for each value = 700

Total for values entered= 21,400

Sum For each value that is > minimum of 700 = 20,200

Sum for the values that did not meet the minimum of 700 = 2,100

Difference desired to total = 2,200

Values that were entered $500,500,200,4000,6000,1000,5000,1000,700,1000,1500$

The Weight for each number I figured to be value Entered / Sum of the values over the 700 miniumum; 4,000/20,200

Weights: $0,0,0, 0.1980 , 0.2970 , 0.0495 , 0.2475, 0.0495 , 0.0347 , 0.0495 , 0.0743$

if > 700 The New value (weight * (Difference Desired To the sum of the values that were > 700 - Sum of non minimums))

$0.1980 (18,000 - 2,100) = 3,148.51$ Else 700

Any assistance is greatly appreciated, I have this in an excel spreadsheet.

Thanks

-
Your question is very unclear. If the original values are $x_i$ and the total $\sum_i x_i = S$ while you want it to be $E$, how are you proposing to change the values? – Robert Israel May 21 '12 at 23:32

So, you have $$500+500+200+4000+6000+1000+5000+1000+700+1000+1500=21400$$ But you want to jiggle the numbers so they add up to 18000, and so that each number is at least 700. Here is one way to do this.
First, replace each number under 700 with 700: $$700+700+700+4000+6000+1000+5000+1000+700+1000+1500=22300$$ Now let $$\alpha={18000-7700\over22300-7700}={10300\over14600}=.70548$$ It should be clear where the 18000 and the 22300 come from. The 7700 comes from the number of numbers, 11, times the minimum, 700. Now take each number on your list, subtract 700, multiply by $\alpha$, and add 700. For example, 4000 becomes $4000-700=3300$, then $3300\alpha=2328.08$, then $2328.08+700=3028.08$. The new numbers will add up to 18000 (up to rounding errors), will all be at least 700, and will be more-or-less proportional to the numbers you started with.
I am applying the .70548 to each of the 11 numbers in the way specified: subtract 700, multiply by .70548, add 700. But if you have a new list of numbers, and a new expected total, you'll have to calculate a different $\alpha$. But I've told you how to compute the new $\alpha$. The numerator is the expected total minus what you'd have if each number was the minimum. The denominator is the actual total, after you bump the small numbers up to the minimum, minus what you'd have if each number was the minimum. – Gerry Myerson May 22 '12 at 1:47