openxlsx writing if formula from R to excel

Question

I am trying to export from R to excel an dataframe with two columns which I want to fill with the excel if formula so that the user could later on change the thresholds. My question is how can I export a formula like the following IF(C2>4; "YES";"NO") to every cell of a new column into excel.

The R code :

library(openxlsx)
library(dplyr)
export_df<-   mtcars %>% tibble::rownames_to_column(var="carname")

# Formulas in EXCEL IF(C>4; "YES";"NO") IF(E>100; "YES";"NO") 

export_df$many_cyl <- paste(paste(paste0(paste0("IF(C" ,seq(2,nrow(export_df)+1 ,1))," > 4")," 'Yes' ", sep=";")," 'No') ", sep=";")

export_df$fast_car <-  paste(paste(paste0(paste0("IF(E" ,seq(2,nrow(export_df)+1,1))," > 100")," 'Yes' ", sep=";")," 'No')", sep=";")

class(export_df$many_cyl) <- c(class(export_df$many_cyl), "formula")
class(export_df$fast_Car) <- c(class(export_df$fast_car), "formula")

openxlsx::addWorksheet(wb,sheetName ="mtcars" )
openxlsx::writeData(wb,"mtcars",export_df )
openxlsx::saveWorkbook(wb, "mtcars.xlsx")

The way I try to create the excel formula does not work. The script fails at the step where I declare the columns to be formulas. The second way to create seperate vectors and export them to a workbook doesnt work either.

How can I solve this?

Ibis

What is the question? – Sukhum

The way I try to create the excel formula does not work. The script fails at the step where I declare the columns to be formulas. The second way to create seperate vectors and export them to a workbook doesnt work either. – Ibis

Answer 1

Your Excel formula is wrong. You need double quotes for yes and no and also commas to separate your arguments not semi colons. Try the following:

export_df$many_cyl <- paste(paste(paste0(paste0('=IF(C' ,seq(2,nrow(export_df)+1 ,1)),
                            ' > 4'),' "Yes" ', sep=','),' "No") ', sep=',')

export_df$fast_car <-  paste(paste(paste0(paste0('=IF(E' ,seq(2,nrow(export_df)+1,1)),
                             ' > 100'),' "Yes" ', sep=','),' "No")', sep=',')


wb <- createWorkbook()
addWorksheet(wb, "Sheet 1")
writeFormula(wb, sheet = "Sheet 1", x = export_df$many_cyl, startCol = 2, startRow = 1)
saveWorkbook(wb, "writeFormulaExample.xlsx", overwrite = TRUE)

Syllabi

Answer 2

On Excel for Mac arguments must be separated by semi colons.

Harhay

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Potomac