0

I have inputs in my data like below and i need to calculate a value using the below values in R

E7 = 20000

EXCEL VIEW(data)

          L             M          N         O
 6     Min_Benefs   Max_Benefs  MSR_Min   MSR_Max
 7     5000         5999        0.039     0.036
 8     6000         6999        0.036     0.034
 9     7000         7999        0.034     0.032
10     8000         8999        0.032     0.031
11     9000         9999        0.031     0.03
12     10000        14999       0.03      0.027
13     15000        19999       0.027     0.025
14     20000        49999       0.025     0.022
15     50000        59999       0.022     0.02
16     60000        0.02                  0.02

from the above data and inputs i need to find out the value of MSR using below formula in R

MSR = ( VLOOKUP($E$7,$L$7:$O$16,3)*(VLOOKUP($E$7,$L$7:$O$16,2) - $E$7) + VLOOKUP($E$7,$L$7:$O$16,4)*($E$7 - VLOOKUP($E$7,$L$7:$O$16,1)))/(VLOOKUP($E$7,$L$7:$O$16,2) - VLOOKUP($E$7,$L$7:$O$16,1))

I got MSR = 0.025 when my E7 = 20000

I tried doing it in R using different functions mentioned in the below link
How to do vlookup and fill down (like in Excel) in R?

But i failed all the time, it would be great if some one explains the solution for this.

Community
  • 1
  • 1
Surendra babu Pasumarhti
  • 453
  • 1
  • 7
  • 16
  • 1
    Can you post some code? The link you have there is correct. You must be doing something wrong. In R, once you have your data in a data frame you can join it to other frames quite easily. But you might have issues with data type conversions and things like that. – JoshK Aug 09 '16 at 14:44
  • I can do it using match command but the problem is.. how to lookup in two columns at the same time and how to select the index. – Surendra babu Pasumarhti Aug 10 '16 at 05:51
  • 1
    If you post the code you have so far in R I will fix it for you. – JoshK Aug 10 '16 at 11:43

1 Answers1

0

1

let's say

ass_benif = 20000

and "msr_benif" is the data that i pasted in the question.

MSR = ((msr_benif$MSR_Min[match(ass_benif,msr_benif$Min_Benefs)])*(msr_benif$Max_Benefs[match(ass_benif,msr_benif$Min_Benefs)] - ass_benif) +
(msr_benif$MSR_Max[match(ass_benif,msr_benif$Min_Benefs)])*(ass_benif -   msr_benif$Min_Benefs[match(ass_benif,msr_benif$Min_Benefs)])) / 
((msr_benif$Max_Benefs[match(ass_benif,msr_benif$Min_Benefs)]) - (msr_benif$Min_Benefs[match(ass_benif,msr_benif$Min_Benefs)]))

The above code is the decoded code in R for the above vlookup function.

2

what if our input is not existed in the data and what if we need to check for our input from the first two columns in the data.

ass_benif = 55555  
vec <- unlist(msr_benif[,c(1,2)]) # create vector
near_ass_benif = as.numeric(vec[which.min(abs(vec - ass_benif))])

a = ifelse(is.na(match(near_ass_benif,msr_benif$Min_Benefs)) == T, match(near_ass_benif,msr_benif$Max_Benefs) ,match(near_ass_benif,msr_benif$Min_Benefs))


MSR = ((msr_benif$MSR_Min[a])*(msr_benif$Max_Benefs[a] - ass_benif) +
   (msr_benif$MSR_Max[a])*(ass_benif - msr_benif$Min_Benefs[a])) / 
    ((msr_benif$Max_Benefs[a]) - (msr_benif$Min_Benefs[a]))
Community
  • 1
  • 1
Surendra babu Pasumarhti
  • 453
  • 1
  • 7
  • 16