I have data that has a structure similar to the one presented
df1 <- read.table(text = "value_1 pred_1 pred_2
20.4 15.188 13.15
20.3 4.849 12.54
20.2 1.115 26.54
20.3 13.136 15.65", header = TRUE)
My goal is to find such a value of "ratio" that the results result = ratio * pred_1 + (1-ratio)*pred_2 are as close as possible to the value_1. If ratio were to be negative, then ratio takes the value 0. The results presented below were obtained in Excel (Goal Seek)
df1.r <- read.table(text = "value_1 pred_1 pred_2 ratio result
20.4 15.188 13.15 3.55740922473013 20.4
20.3 4.849 12.54 0 12.54
20.2 1.115 26.54 0.249360865290069 20.2
20.3 13.136 15.65 0 16.6
", header = TRUE)
There is a simple closed form algebraic solution to this trivial problem.
v = r * p1 + (1 - r) * p2
Rearranges easily to give a formula for r namely
r = (v - p2)/(p1 - p2)
if (r < 0) r = 0;
In a program it also needs defending from p1==p2 as well and you need to decide what answer is appropriate in that special case (when r -> +infinity).
We have the target value v and the predictors p1 and p2 so substituting them gives the answer r for the ratio that you seek.
In Excel assuming column A is v, B isp1, C is p2The the formula=(A2-C2)/(B2-C2)` will give the ration required.
(NB with no safeguards against overflow or negative values)