Excel Weight Solver Script!

[infraxo]

I would like to make a weight solver, such that:

If I am given three values A B C and a fourth value D, the script will calculate the best approximate weights of A B and C that will give D.

For example, given

A = 10
B = 5
C = 2
and
D = 6.136

I would like the script to calculate approximations such as:

wA = 0.4
wB = 0.31
wC = 0.29

or indeed the exact answer (if it exists):

wA = 0.4
wB = 0.312
wC = 0.288

Please help me! Anything would be appreciated.

 

[pspartan13]

Hey Info,

I'm confused about your problem. Why wouldn't the "weights" be the following?

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=64 align=right>0.588235</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.294118</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.117647</TD></TR></TBODY></TABLE>

These would correspond on a % basis with.

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=64 align=right>10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>2</TD></TR></TBODY></TABLE>

Right?

If my post makes sense, invite me to propose on your elance project on this subject, i can help you make something more robust. My elance screenname is pspartan13.

Thanks
Rob

 

[shg]

Without other criteria, there are an infinite number of solutions. You can pick any two weights at random and solve for the third.

Even if the weights are constrained to be non-negative, there are either zero, one, or an infinite number of solutions, and no one choice is better than any other.

 

[pspartan13]

shg is, i believe, correct

your use of the word "weight" made me think you wanted an answer with the same ratios though (ie A to B is the same ratior as w_A to w_B) if thats the case there is only one answer which i believe is the numbers i posted

if this isn't what you meant, then shg is correct and SOLVER will give you whatever answer it converges on first.