Writing Lamda functions

[JanMarvin]

This SO asked how to write some Lamda formula. Since this requires some modern spreadsheet formula, we have to slightly modify the formula. Here's an openxlsx2 answer:

library(openxlsx2)

data <- structure(
  list(yes = c(2, 0), no = c("2A", "4A"), yes = c("3.5A", "3A"),
       yes = c(NA, NA), no = c(-1, NA), yes = c(0, 1)),
  class = "data.frame",
  row.names = c("values", "values2")
)


wb <- wb_workbook()$add_worksheet()$
  add_data(dims = wb_dims(x = data, from_col = 2),
           x = data, na.strings = NULL)

fml_gen <- function(x) {
  fml <- sprintf(
    "_xlfn.LET(_xlpm.d, B%s:G%s,
  _xlpm.f, $B$1:$G$1,
  _xlpm.d_, _xlfn.NUMBERVALUE(SUBSTITUTE(_xlpm.d, \"A\", \"\")),
  AVERAGE(_xlfn._xlws.FILTER(_xlpm.d_, (_xlpm.f = \"yes\") * (_xlpm.d_ &gt; =1))))",
    x + 1L, x + 1L
  )
  gsub(" ", "", fml)
}

fml <- fml_gen(seq_len(nrow(data)))

# we are currently not so great at writing array formula vectors
wb$add_formula(
  dims = "A2",
  x = fml[1],
  array = TRUE)

wb$add_formula(
  dims = "A3",
  x = fml[2],
  array = TRUE)

if (interactive()) wb$open()