I have installed the ExcelFunctionsR package in R and am trying to use the MINIFS formula, and am coming across a particular issue when trying to reference a specific cell in my formula. The excel formula can be seen in the image below.
I have tried out the R version of it, and it runs fine, but I'm trying to get the "[@[Account Name]]" part of the excel formula to work all the way down my column as it does in excel. I am trying to find the "First Meeting Date" for each Account Name. I already know how to add in the column for "First Meeting Date". Below is where I'm currently stuck in my R formula.
MINIFS(Activities$Date, Activities$Account.Name, Activities$Account.Name[1:390493],
Activities$Enterprise.Activity.Type, "Meeting")
#> Error in MINIFS(Activities$Date, Activities$Account.Name, Activities$Account.Name[1:390493], : could not find function "MINIFS"
I want to have "Activities$Account.Name[1:390493]" reference each adjacent Account Name going down the entire column the same way the "[@[Account Name]]" part of the excel formula does. Any clue how to do this?
Hi @griffin6,
If I get your example correct, you group by Account Name, filter by Type == "Meeting" and then aggregate/summarise the Dates by minimum.
I'm not sure if your example is comprehensive (as there is no Activity Type "Meeting"), but here is an example how to do a grouped summary of the table by the minimum Date in R.
library(tidyverse)
library(lubridate)
#>
Example <- data.frame(stringsAsFactors = FALSE, Date = c("8/4/2020",
"11/26/2019", "12/4/2019", "1/29/2020", "2/5/2020"), Account.Name = c("Apple",
"NRG Energy", "Principal Financial Group", "Willis Towers Watson",
"Salesforce.com"), Enterprise.Activity.Type = c("Task", "Task",
"Task", "Task", "Task"))
Example %>%
as_tibble() %>%
# cast to a proper Date type as the chosen string format does not allow for correct sorting
mutate(Date = as_date(Date, format = "%m/%d/%Y")) %>%
# you could add
# filter(Enterprice.Activity.Type == "Meeting") %>% here
# but the example will be empty
# group by Name and Type
group_by(Account.Name, Enterprise.Activity.Type) %>%
summarise(MIN_DATE = min(Date))
#> # A tibble: 5 x 3
#> # Groups: Account.Name [5]
#> Account.Name Enterprise.Activity.Type MIN_DATE
#> <chr> <chr> <date>
#> 1 Apple Task 2020-08-04
#> 2 NRG Energy Task 2019-11-26
#> 3 Principal Financial Group Task 2019-12-04
#> 4 Salesforce.com Task 2020-02-05
#> 5 Willis Towers Watson Task 2020-01-29
this means that there is no MINIFS function available for you to use.
If MINIFS is provided by ExcelFunctionsR which you have installed, then you would use library('ExcelFunctionsR') to load the library so that MINIFS becomes available to you.