Avoid CSE in formula using openxlsx?

Question

I am trying to come up with a formula for openxlsx that

• a) averages all non-zero/non-negative values (including those with trailing As)
• b) under the condition that the column-name constains "yes"
• c) requires no user input (such as ctrl+shift+enter)

The solutions I and others came up with require some form of (unwanted) user input.

library(reprex)
library(openxlsx)

values <- c(2, "2A", "3,5A", "", "-1", "0")
values2 <- c(0, "4A", "3A", "", "", "1")
df <- rbind(values,values2) |> as.data.frame()
colnames(df) <- c("yes1", "no1", "yes2", "yes3", "no2", "yes4")

df
#>         yes1 no1 yes2 yes3 no2 yes4
#> values     2  2A 3,5A       -1    0
#> values2    0  4A   3A             1

# works but requires user-input
formula_row1 <- '=AVERAGE(IF((ISNUMBER(SEARCH("yes",$A$1:$F$1)))*(NUMBERVALUE(SUBSTITUTE(A2:F2, "A", ""))>=1), NUMBERVALUE(SUBSTITUTE(A2:F2, "A", "")), ""))'

wb <- createWorkbook()
addWorksheet(wb, "test")
writeData(wb, "test", x = df)
writeFormula(wb, "test", x = formula_row1 , startCol = 7, startRow = 2, array = T)
saveWorkbook(wb, "test.xlsx", overwrite = T)

^{Created on 2024-02-17 with reprex v2.1.0}

The expected output for the first row would be 2.75.

Any solution is more than welcome :)