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

IF(AND(

=IF(AND(A1="this",B1="that"),"x","")

IF(OR(

=IF(OR(A1="this",A1="that"),"x","")

Data Table

will prompt you to do:

row input cell

column input cell

*gotta get the dollar signs between the letter

SUMPRODUCT

=SUMPRODUCT (array1, [array2], ...)

array1 - The first array or range to multiply, then add.

array2 - [optional] The second array or range to multiply, then add.

VLOOKUP

=VLOOKUP (value, table, col_index, [range_lookup])

value - The value to look for in the first column of a table.

table - The table from which to retrieve a value.

col_index - The column in the table from which to retrieve a value.

range_lookup - [optional] TRUE = approximate match (default). FALSE = exact match.

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.

ISERR

-ISERR(value)

value - The value to check for any error but #N/A.

IFERROR

=IFERROR (value, value_if_error)

SUMSQ

=sumsq(num1,num2)