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,
  ...
)

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?

...

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.

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_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_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)