Can someone help in rewriting this Excel formula in R Studio?

General
1

I have this long formula in Excel that I want to replicate in R Studio, because Excel takes a lot of time to calculate it on a large data set.

=IF(AND($DE4<BA$1,$DF4>=BA$1,$DF4<=BA$2),($DF4-BA$1+1)/($DF4-$DE4+1),IF(AND($DE4<BA$1,$DF4>BA$2),(BA$2-BA$1+1)/($DF4-$DE4+1),IF(AND($DE4>=BA$1,$DE4<=BA$2,$DF4<=BA$2,$DF4>=BA$1),1,IF(AND($DE4>=BA$1,$DE4<=BA$2,$DF4>BA$2),(BA$2-$DE4+1)/($DF4-$DE4+1),0))))*$DH4

BA1 = 1 Jan 2016 (Its a date)
BA2 = 31 Mar 2016 (Its a date)
DE4 = 12 Apr 2016 (Its a date
DF4 = 10 June 2017 (Its a date)
DH4 = 1795 (Its an integer)

Have found that writing longer ifelse statements is very difficult in R Studio, especially if dates are involved. Is there an easy way of doing that?

2

You can use dplyr::case_when()

If you need more specific help, please provide a proper REPRoducible EXample (reprex) illustrating your issue.

3

One seldom write IF statements in R. (RStudio is "just" the IDE. R is the stats and graphics environment. There is almost always a better way to do it in R.

You need to explain to us, in substantive terms what you are trying to do.

Start out with a general statement of the project and what kind of analysis you are doing.
What do you want to do.
What is the final outcome of the analysis?

Do you have R code for other parts of your project? If so please supply it.

Also please supply some sample data in .csv or dput() format.

Here is some suggestions on creating an example. They may not be genneally applicable in this case but the instruction on supplying sample data are important (i.e. dput()) Blast it forgot the link.

It is important not to say how you want to do something but what you want to do. If you are used to Excel, you have just entered an alien universe. :slight_smile:

4

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.