ExcelFunctionsR Question

[griffin6]

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.

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"))

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?

[smichal]

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

^{Created on 2020-10-12 by the reprex package (v0.3.0)}

[nirgrahamuk]

@griffin6

could not find function "MINIFS"

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.

Remember, Install once, library when needed