0
$\begingroup$

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

  • 0
    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?2012-05-21

1 Answers 1

2

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.

  • 0
    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!2012-05-22