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

-
Gerry, Thank you for your response. To say this is WAY beyond my math abilities is putting it mildly... so may I ask, since some numbers are larger than other numbers does applying .70548 to each of the numbers accuratly weight the value..This is for premim product A is worth 6000, Product B is worth 1000 to get at and so forth, to get at the desired to above, is it correct that the same rate would be applied to the differnt products...sorry, I am trying to explain this as best as possible..Thanks again –  Kristen May 22 '12 at 1:27
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
Gerry thanks again, I was playing w/ different miniums and values in excel utilizing formulas for the min and other values..your answer has been working pefectly..it is just much different approach to how I thought the calculation would be...I thought I had to apply a weight first and then alter the weight...thanks again. This is the first time I have utilized this site, I accepted the answer, if there is another way to give u more points, just let me know..Thanks! –  Kristen May 22 '12 at 2:07