Skip to contents

Add a formula to a cell range in a worksheet

Source: R/class-workbook-wrappers.R
wb_add_formula.Rd

This function can be used to add a formula to a worksheet. In wb_add_formula(), you can provide the formula as a character vector.

Usage

wb_add_formula(
  wb,
  sheet = current_sheet(),
  x,
  dims = wb_dims(start_row, start_col),
  start_col = 1,
  start_row = 1,
  array = FALSE,
  cm = FALSE,
  apply_cell_style = TRUE,
  remove_cell_style = FALSE,
  enforce = FALSE,
  shared = FALSE,
  name = NULL,
  ...
)

Arguments

wb

A Workbook object containing a worksheet.

sheet

The worksheet to write to. (either as index or name)

x

A formula as character vector.

dims

Spreadsheet dimensions that will determine where x spans: "A1", "A1:B2", "A:B"

start_col

A vector specifying the starting column to write to.

start_row

A vector specifying the starting row to write to.

array

A bool if the function written is of type array

cm

A special kind of array function that hides the curly braces in the cell. Add this, if you see "@" inserted into your formulas.

apply_cell_style

Should we write cell styles to the workbook?

remove_cell_style

Should we keep the cell style?

enforce

enforce dims

shared

shared formula

name

The name of a named region if specified.

...

additional arguments

Value

The workbook, invisibly.

Details

Currently, the local translations of formulas are not supported. Only the English functions work.

The examples below show a small list of possible formulas:

  • SUM(B2:B4)

  • AVERAGE(B2:B4)

  • MIN(B2:B4)

  • MAX(B2:B4)

  • ...

It is possible to pass vectors to x. If x is an array formula, it will take dims as a reference. For some formulas, the result will span multiple cells (see the MMULT() example below). For this type of formula, the output range must be known a priori and passed to dims, otherwise only the value of the first cell will be returned. This type of formula, whose result extends over several cells, is only possible with single strings. If a vector is passed, it is only possible to return individual cells.

Custom functions can be registered as lambda functions in the workbook. For this you take the function you want to add "LAMBDA(x, y, x + y)" and escape it as follows. LAMBDA() is a future function and needs a prefix _xlfn. The arguments need a prefix _xlpm.. So the full function looks like this: "_xlfn.LAMBDA(_xlpm.x, _xlpm.y, _xlpm.x + _xlpm.y)". These custom formulas are accessible via the named region manager and can be removed with wb_remove_named_region(). Contrary to other formulas, custom formulas must be registered with the workbook before they can be used (see the example below).

See also

Other workbook wrappers: base_font-wb, col_widths-wb, creators-wb, grouping-wb, row_heights-wb, wb_add_chartsheet(), wb_add_data(), wb_add_data_table(), wb_add_hyperlink(), wb_add_pivot_table(), wb_add_slicer(), wb_add_worksheet(), wb_base_colors, wb_clone_worksheet(), wb_copy_cells(), wb_freeze_pane(), wb_merge_cells(), wb_save(), wb_set_last_modified_by(), wb_workbook()

Other worksheet content functions: col_widths-wb, filter-wb, grouping-wb, named_region-wb, row_heights-wb, wb_add_conditional_formatting(), wb_add_data(), wb_add_data_table(), wb_add_hyperlink(), wb_add_pivot_table(), wb_add_slicer(), wb_add_thread(), wb_freeze_pane(), wb_merge_cells()

Examples

wb <- wb_workbook()$add_worksheet()
wb$add_data(dims = wb_dims(rows = 1, cols = 1:3), x = c(4, 5, 8))

# calculate the sum of elements.
wb$add_formula(dims = "D1", x = "SUM(A1:C1)")

# array formula with result spanning over multiple cells
mm <- matrix(1:4, 2, 2)

wb$add_worksheet()$
 add_data(x = mm, dims = "A1:B2", col_names = FALSE)$
 add_data(x = mm, dims = "A4:B5", col_names = FALSE)$
 add_formula(x = "MMULT(A1:B2, A4:B5)", dims = "A7:B8", array = TRUE)

# add shared formula
wb$add_worksheet()$
 add_data(x = matrix(rnorm(5*5), ncol = 5, nrow = 5))$
 add_formula(x = "SUM($A2:A2)", dims = "A8:E12", shared = TRUE)

# add a custom formula, first define it, then use it
wb$add_formula(x = c(YESTERDAY = "_xlfn.LAMBDA(TODAY() - 1)"))
#> formula registered to the workbook
wb$add_formula(x = "=YESTERDAY()", dims = "A1", cm = TRUE)
#> Warning: modifications with cm formulas are experimental. use at own risk