Excel Formulas 1st Study Se

COUNT

(value1, [value2], ...) - counts the number of cells in a range of cells that contain numbers

$A$1

Both the column and row references are "absolute" and will remain constant when the reference is copied and pasted to other cells.

VLOOKUP

Looks for the value in the leftmost column of a table, and then returns a value in the same row from a column you specify

AVERAGE

Returns the average of its arguments, which can be numbers or names, arrays, or references that contain numbers

MAX

Returns the maximum value in a list of arguments

NOT

(logical) - accepts a single Boolean expression as an argument and returns the opposite of that expression. If the Boolean expression evaluates as TRUE then the result of the NOT function is FALSE. Likewise, if the expression evaluates as FALSE then the result of the NOT function is TRUE.

RATE

(nper, pmt, pv, [fv], [type], [guess]) - calculates the interest rate earned for an investment given the number of payments made as part of the investment, the payment amount, and the current value of the investment.

FV

(rate, nper, pmt, [pv], [type]) - calculates the future value of an investment given the interest rate, number of payments to be made, and the amount of the payment.

PMT

(rate, nper, pv, [fv], [type]) - calculates the payment amount for a loan given the interest rate, number of payments to be made to pay off the loan, and the original loan amount.

NPER

(rate, pmt, pv, [fv], [type]) - calculates the number of payments that will be made to pay off a loan given the interest rate, payment amount, and original loan amount.

SUM

Adds all the numbers in a range of cells (We use this a LOT to roll up trial balances and sub ledgers

SUMIF

Adds the cells specified by a given criteria (We use this a LOT to roll up trial balances and sub ledgers) SYNTAX The syntax for the SUMIF function in Microsoft Excel is: SUMIF( range, criteria, [sum_range] ) Parameters or Arguments 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. It is the cells to sum. If this parameter is omitted, it uses range as the sum_range.

SUMIFS

Adds the cells specified by a given set of criteria or conditions (We use this a LOT to roll up trial balances and sub ledgers)

IF

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE The syntax for the IF function in Microsoft Excel is: IF( condition, [value_if_true], [value_if_false] ) Parameters or Arguments condition The value that you want to test. value_if_true Optional. It is the value that is returned if condition evaluates to TRUE. value_if_false Optional. It is the value that is return if condition evaluates to FALSE.

AND

Checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE

OR

Checks whether any of the arguments are TRUE, and then returns TRUE or FALSE. Returns FALSE only if all arguments are FALSE

COUNTIFS

Counts the number of cells specified by a given set of conditions or criteria

COUNTIF

Counts the number of cells within a range that meet the given condition

AVERAGEIF

Finds average for the cells specified by a given condition or criteria

AVERAGEIFS

Finds average for the cells specified by a given set of conditions or criteria

MID

Returns a specific number of characters from a text string starting at the position you specify

TIME

Returns the number that represents a particular time

MIN

Returns the smallest value in a list of arguments

$A1

The column reference is "absolute" and will remain constant when copied and pasted to other cells. The row reference is "relative" and will change when copied and pasted to cells in other rows of the worksheet.

A$1

The column reference is "relative" and will change when copied and pasted to cells in other columns in the worksheet. The row reference is "absolute" and will remain constant when copied and pasted to other cells.

PV

This formula indicates whether or not an investment will generate sufficient returns in the future =PV(rate, nper, pmt)

MEDIAN

returns the median, or the number in the middle of the set of given numbers