Is there a way in Base R to replicate what VLOOKUP TRUE in Excel does?

user_5587015 0
Viewed 57

I have a consumption pattern that looks like this:

x <-0:10
y<-c(0, 0.05, 0.28, 0.45, 0.78, 0.86, 0.90, 0.92, 0.95, 0.98, 1.00)

X is in years, and Y is not always monotonically-increasing, although it should be most of the time.

If I needed to estimate how many years would elapse before 80% is consumed, in Excel, I would use the VLOOKUP TRUE function which would return 78%, then I would lookup the next value in the series (86%) and then linearly interpolate to get 4.25 years. It's laborious but it gets the job done.

Is there an easy way to compute this in R, in a user-defined function that I can apply to many cases?

Thanks!

vlookup interpolation r
2 Answers
x <- 0:10
y <- c(0, 0.05, 0.28, 0.45, 0.78, 0.86, 0.90, 0.92, 0.95, 0.98, 1.00)


estimate_years <- function(x, y, percent) {
  idx <- max(which(y < percent))
  (percent - y[idx]) / (y[idx+1] - y[idx]) * (x[idx+1] - x[idx]) + x[idx]
}

estimate_years(x, y, 0.80) ## 4.25

Although the approx calculation is cool, exact linear interpolation here is easy. idx is the next smaller position for y and x. idx+1 thus is the next equal/bigger position for y and x in relation to percentage.

Through triangular calculation, where k = part / total which is (percent - y[idx]) / (y[idx+1] - y[idx])

and applying k * total_x represented here by k * (x[idx+1] - x[idx]) - the result of the linear interpolation and adding last smaller years x[idx], we obtain the result.

user_96900900

You could try with approx

resolution <- 1000
fn <- approx(x, y, n=resolution)
min(fn$x[fn$y > 0.8])
[1] 4.254254

The better you need your estimate to be, use a higher number for resolution

user_79583560
Related