MGT 2210 - EXCEL SPECIFIC FORMULAS

SUMIF

=SUMIF (range, criteria, [sum_range])

range - The range of cells that you want to apply the criteria against.

criteria - The criteria used to determine which cells to add.

sum_range - [optional] The cells to add together. If sum_range is omitted, the cells in range are added together instead.

SUMIFS

=SUMIFS (sum_range, range1, criteria1, [range2], [criteria2], ...)

sum_range - The range to be summed.

range1 - The first range to evaulate.

criteria1 - The criteria to use on range1.

range2 - [optional] The second range to evaluate.

criteria2 - [optional] The criteria to use on range2.

AVERAGEIF

=AVERAGEIF (range, criteria, [average_range]

range - One or more cells, including numbers or names, arrays, or references.

criteria - A number, expression, cell reference, or text.

average_range - [optional] The cells to average. When omitted, range is used.

AVERAGEIFS

=AVERAGEIFS (avg_rng, range1, criteria1, [range2], [criteria2], ...)

avg_rng - The range to average.

range1 - The first range to evaulate.

criteria1 - The criteria to use on range1.

range2 - [optional] The second range to evaluate.

criteria2 - [optional] The criteria to use on range2.

COUNT

=COUNT (value1, [value2], ...)

COUNTIF

=COUNTIF (range, criteria)

range - The range of cells to count.

criteria - The criteria that controls which cells should be counted.

COUNTIFS

=COUNTIFS (range1, criteria1, [range2], [criteria2], ...)

range1 - The first range to evaulate.

criteria1 - The criteria to use on range1.

range2 - [optional] The second range to evaluate.

criteria2 - [optional] The criteria to use on range2.

Goal-seeking analysis

represents a "backward" solution approach. It attempts to calculate the value of the inputs necessary to achieve a desired level of output.

IFS

=IFS (test1, value1, [test2, value2], ...)

test1 - First logical test.

value1 - Result when test1 is TRUE.

test2, value2 - [optional] Second test/value pair

XLOOKUP

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

MATCH

=MATCH (lookup_value, lookup_array, [match_type])

lookup_value - The value to match in lookup_array.

lookup_array - A range of cells or an array reference.

match_type - [optional] How to match, specified as -1, 0, or 1. Default is 1.

INDEX

=INDEX (array, row_num, [col_num], [area_num])

array - A range of cells, or an array constant.

row_num - The row position in the reference or array.

col_num - [optional] The column position in the reference or array.

area_num - [optional] The range in reference that should be used.

IFERROR

=IFERROR (value, value_if_error)