Inverse SUMPRODUCT?

[Moosles]

I'm probably missing something obvious here but I can't seem to work this out for the life of me. I have a target value that I am looking to aim for, and I have 2 variables which I want to apply proportionally to get a weighted average of that target value.

i.e. I have a target of 5.5, and variables of 2 and 6. I want to know how much of each variable I need to use in order to obtain a weighted average of the target value. In this case the ratio of Variable 1 to Variable 2 is 1:7, but I found this through trial and error applying a weighted SUMPRODUCT

=SUMPRODUCT(A2:B2,A3:B3)/SUM(A3:B3)

and manually adjusting the percentages rather than any kind of formulaic approach.

Variable 1	Variable 2
2	6
12.5%	87.5%

I would like to find a formula that tells me what the % would need to be for A3 and B3 in order to obtain a weighted average. Is this possible?

 

[Vogateer]

Are you only going to have two variables? I haven't done this sort of algebra in a while, but I can give it my best.

 

[Vogateer]

	A	B	C	D
1	Variable 1	Variable 2		Target Value
2	2​	6​		5.5
3	12.50%​	87.50%​

Target = D2
Variable1 = A2
Variable2 = B2

A3 =

=(Target-Variable2)/(Variable1-Variable2)

B3 =

1 - A3

 

[Vogateer]

Did those formulas work for you?

 

[Moosles]

This was exactly what I needed, thank you. I actually have 3 variables but I was able to use what you provided and expand as needed. Thanks for your help!