I have the following table with cells containing numbers from -1 to 10 (decimal places allowed). In some cases there is a trailing A behind the number.
Now, I would like to extract all digits from the cells and calculate the average per row using a formula but only consider elements that fulfill certain conditions:
a) The header does not contain "no" and
b) the value is >= 1, otherwise the value should not count towards the average.
The expected outcome is listed in the average column.
| average | yes | no | yes | yes | no | yes |
|---|---|---|---|---|---|---|
| 2.75 | 2 | 2A | 3.5A | -1 | 0 | |
| 2 | 0 | 4A | 3A | 1 |
I already got an very helpful answer how to solve this using an advanced formula but now I am unable to implement this (or any other) solution using openxlsx
library(openxlsx)
values <- c(2, "2A", "3A", "", "-1", "0")
values2 <- c(0, "4A", "3A", "", "", "1")
df <- rbind(values,values2)
colnames(df) <- c("yes", "no", "yes", "yes", "no", "yes")
formula <- '=LET(data,B1:G3,
f,TAKE(data,1),
d_,NUMBERVALUE(SUBSTITUTE(DROP(data,1),"A","")),
BYROW(d_,LAMBDA(r,AVERAGE(FILTER(r,(f="yes")*(r>=1))))))'
wb <- createWorkbook()
addWorksheet(wb, "test")
writeData(wb, "test", x = df)
writeFormula(wb, "test", x = formula, startCol = 8, startRow = 1)
saveWorkbook(wb, "test.xlsx", overwrite = T)
When I try to open the file it say "We found a problem with some of the contents in test.xlsx". However, when I manually add the formula after the fact it works just fine.
Any help is appreciated!