Excel Expert Functions for Certification

CHOOSE Function

Chooses a value or action to perform from a list of values, based on an index number

AVERAGEIF

Finds the average value for a range of cells, but only if those values meet specific criteria

HLOOKUP function

A lookup function that searches horizontally across the top row of the lookup table and retrieves the value in the column you specify.

VLOOKUP function

A lookup function that searches vertically across the leftmost column of the lookup table and retrieves the value in the row you specify.

SUMIFS

Adds the cells in a range that meet multiple criteria

SUMIF

Calculates a sum from a group of values, but just of values that are included because a condition is met

OR Function

Checks whether ANY arguments are TRUE and Returns TRUE if ANY are true

AND Function

Checks whether all arguments are TRUE and Returns TRUE when ALL are true

WEEKDAY

Converts a serial number to a days of the week

COUNTIF

Counts the number of cells in a range that match a criteria

COUNTIFS

Counts the number of cells within a range that meet multiple criteria

AVERAGEIFS

Returns the average (arithmetic mean) of all cells that meet multiple criteria

NOW

Returns the system date and time

TODAY

Returns the system date without the time

MATCH

Returns up the relative position of an item in an array that matches a specified value in a specified order

NOT Function

Reverses the logic of its argument: Changes FALSE to TRUE and vice versa

IF Function

Tests for a true or false condition and then returns one value or another