Need a Weight% to Reach Goal Formula

Hello All,

Please find attach my problem.

I need to fill column B with a weight percentage, but my "Volume Item" and "Bonus Per Item" columns must stay the same to achieve the weighted average goal of $38.81. My "Weight %" must equal to 100% and "Total Bonus" will equal "Total Bonus Goal" of $1,048,929.84 if "Weight %" are correct. I know there will be variable answers, but any answer will be fine.

Pleaseee any help with this will be truly appreciated.

Thank you,

As with the other problem, I would start this as an algebra problem before making it a programming problem. In the previous problem we had 1 equation, so we could assign any random value to all but one of the weights, and then solve for the final weight. This case is the same, but we have a system of 2 equations, so we assign random values to 9 of the 11 weights and solve for the other 2.

eq'n 1 weighted average: 38.81=A=sum(wi*xi) <-- Should already know how to solve this for w1 from earlier today.
eq'n 2 weights: 1=Wtot=sum(wi)

Shall I give you a chance to try the system of equations yourself? (A tutorial for simple systems of equations if you need an algebra refresher: https://www.purplemath.com/modules/systlin1.htm )

I find that, before I can come up with a spreadsheet formula, I have to solve the algebra problem. In this case, I might solve the system of equations like this:

From earlier w1=(A-sum(for i=2 to n)(wi*xi))/x1
solve eq'n 2 for w1 w1=1-sum(for i=2 to n)(wi)
set the two expression for w1 equal to each other 1-(w2+w3+...+wn)=(A-(w2x2+w3x3+...+wnxn))/x1
Solve this for w2
first multiply by x1: x1-x1(w2+w3+...+wn)=A-(w2x2+w3x3+...+wnxn)
need to isolate the w2 terms w2x2-x1w2=x1(w3+...+wn)+A-(w3x3+...+wnxn)-x1
divide by x2-x1 w2=(x1*(w3+...+wn))+A-(w3x3+...+wnxn)-x1)/(x2-x1)

Now I can adapt that formula to spreadsheet syntax and enter that formula into B7. Then choose either eq'n 1 or eq'n 2 (eqn'2 is easier) and solve for w1. Enter that formula into B6

B7 =(D2-sumproduct(B8:B16,D8:D16)-D6+D6*sum(B8:B16))/(D7-D6)
B6=1-SUM(B7:B16)

From there, it is a matter of entering reasonable and/or random values into B8:B16.

Have you understood what we have done so far? Because, from here, everything is just different applications and variations on this "solve a system of equations" approach that we are currently using. If you are not understanding how this works so far, then you will probably have trouble understanding what happens next.

So the main question at this point is how to choose the random numbers. I notice that the target (38.81 in your example) is between the largest value (41.05) and the second largest value(37.65). This tells me that any valid solution where all of the weights are between 0 and 1 will need to include some non-zero number in B16, and may heavily favor this value. I reformulate my solution so that, instead of solving for B6, I solve for B16 (B6 now includes a random number, and B16 is =1-sum(B6:B15)).

At this point, I ask myself what happens if I assume every wi is 0 except for two. So I look at what happens if I assume all but two wis are 0, and I end up with wi=(A-xmax)/(xi-xmax). Enter that formula into column J [J6=($D$2-$D$16)/(D6-$D$16)] and copy down into J7:J15. This column now represents the largest value that B6:B15 can have (assuming everything else is 0). No I can use these values to inform how I choose the random numbers to put into B6:B15.

Does that help?

Administrative Note:

Please don't quote entire posts unnecessarily. They clutter threads and make them hard to read.
Use the "Quick reply" instead
Thanks

I can't upload a file, but here are the edits I would make in your file:

In J6 enter the formula I recently mentioned =($D$2-$D$16)/(D6-$D$16). copy/paste/fill down to J15
In B6 enter =RAND()/10 (since there are about 10 values). Copy/paste/fill down to B15
In B15 enter =1-SUM(B6:B15)

Now examine the results and see what you think. Try different values in place of the 10, if you like. From there, we could also explore other ways to generate these random numbers.

Forgot the other formula in one of the other wi cells. Which weight do you want tosolve for using =($D$2-SUMPRODUCT(other weights, other bonus)-$D$16+$D$16*SUM(other weights))/(this bonus-$D$16)?

That just means tweaking the random numbers in B6:B14.

You could try dividing by a larger number (maybe 12 or 20). =RAND()/12 or =RAND()/20. If you want to get serious about trying different values, enter 10 into A2 (or other cell), and use =RAND()/$A$2. Now you can try different divisors by entering whatever you want to try into A2.

Or try incorporating the values in column J to inform these random numbers =RAND()*J6/10 (entered in B6 and copied/filled into B6:B14).
Or any other suitable algorithm for choosing the values in B6:B14.