I want to implement the following formula using openxlsx: =IF(COUNTIF(E3:G3,”none“)=3,”N/A”,AVERAGE(IF(ISNUMBER(–E3:G3),–E3:G3,””))) to each row
This formula works perfectly in excel given the dataframe, however when I use openxlsx, it gives me the following:
=IF(COUNTIF(E3:G3, “none“) = 3, “N/A”, AVERAGE(IF(ISNUMBER(–@E3:G3), –@E3:G3, “”)))
and doesn’t give me the correct answer, how do I fix this and get rid of the @ symbol.

library(openxlsx)

# Create a new workbook and add a worksheet
wb <- createWorkbook()
addWorksheet(wb, "Sheet1")

# Example data: write some sample data to columns E, F, and G starting from row 1
example_data <- data.frame(
  E = c(1, NA, 3, "none", 5, 6, 7, 8, 9, 10), 
  F = c(11, 12, "none", 14, 15, 16, 17, 18, 19, 20),
  G = c(21, 22, 23, 24, "none", 26, 27, 28, 29, 30)
)
writeData(wb, "Sheet1", example_data, startCol = 5, startRow = 1)

# Determine the number of rows in your data
num_rows <- nrow(example_data)

# Function to create the formula with conditions
create_custom_formula <- function(row) {
  sprintf(
    "=IF(COUNTIF(E%d:G%d, "*none*") = 3, "N/A", AVERAGE(IF(ISNUMBER(--E%d:G%d), --E%d:G%d, "")))",
    row, row, row, row, row, row
  )
}

# Apply the function to each row and write the formulas to column H
lapply(1:num_rows, function(row) {
  writeFormula(wb, "Sheet1", x = create_custom_formula(row + 1), startCol = 8, startRow = row + 1)
})

# Set the column width for better visibility
setColWidths(wb, "Sheet1", cols = 8, widths = 15)

# Set the header for the new column (H1) to "Average"
writeData(wb, "Sheet1", "Average", startCol = 8, startRow = 1)

# Save the workbook
saveWorkbook(wb, "example_with_custom_averages.xlsx", overwrite = TRUE)