MGSC 290 Chapter 6

RIGHT

Returns a specified number of characters from the end (right side) of a text string

LEFT

Returns a specified number of characters from the beginning (left side) of a text string

MID

Returns characters from the middle of a text string given a starting position and length

Rounding

The process of adjusting a number to a specified number of digits

ROUND

Rounds a number to a specified number of digits

ROUNDUP

Rounds a number up away from zero

ROUNDDOWN

Rounds a number down toward zero

Nested formula

A formula that contains another formula inside it

Nested function

A function used as an argument within another function

Product

The result of multiplying numbers together

SUMPRODUCT

Multiplies corresponding elements in arrays and returns the sum of those products

SUMIF

Adds values that meet a single specified condition

SUMIFS

Adds values that meet multiple specified conditions

AVERAGEIF

Calculates the average of values that meet one condition

AVERAGEIFS

Calculates the average of values that meet multiple conditions

COUNTIF

Counts the number of cells that meet one condition

COUNTIFS

Counts the number of cells that meet multiple conditions

Median

The middle value in a set of numbers arranged in order

MEDIAN

Returns the median value of a given set of numbers

Standard deviation

A measure of how spread out numbers are from the mean

Population

The entire group of data being studied

STDEV.S

Calculates standard deviation based on a sample of data

STDEV.P

Calculates standard deviation based on an entire population

mean absolute deviation (MAD)

The average of the absolute differences between each data value and the mean

absolute value of a number

The distance of a number from zero regardless of direction

mode

The value that appears most frequently in a data set

MODE.SNGL

Returns the most frequently occurring value in a dataset

MODE.MULT

Returns an array of the most frequently occurring values when there are multiple modes

Array

A collection of values organized in rows and/or columns

Ranking

The process of ordering values based on size or position

RANK.EQ

Returns the rank of a number in a list with equal ranks for ties

RANK.AVG

Returns the average rank for tied values

AND

Returns TRUE if all conditions are true

OR

Returns TRUE if at least one condition is true

NOT

Reverses the logical value of its argument

IFS

Evaluates multiple conditions and returns a value for the first true condition

Future value

The value of an investment at a specified date in the future

FV

Calculates the future value of an investment based on interest rate and payments

Present Value

The current value of a future sum of money

PV

Calculates the present value of an investment or loan

Time Value of Money (TVM)

The concept that money today is worth more than the same amount in the future

NPV

Calculates the net present value of a series of cash flows

Discount rate

The interest rate used to determine the present value of future cash flows

NPER

Calculates the number of periods for an investment or loan

Depreciation

The reduction in value of an asset over time

Depreciation schedule

A table showing how an asset’s value decreases over time

Book value

The value of an asset after depreciation is accounted for

Straight-line depreciation

A method that spreads the cost of an asset evenly over its useful life

SLN

Calculates straight-line depreciation for an asset

Accelerated Depreciation

A method that records higher depreciation expenses in earlier periods

Sum-of-the-years' digits (SOYD) depreciation

An accelerated method that applies decreasing fractions over time

SYD

Calculates depreciation using the sum-of-the-years' digits method

Declining balance depreciation

A method that applies a constant rate to the reducing book value

DB

Calculates depreciation using the fixed-declining balance method

Double-declining balance depreciation

An accelerated method that doubles the straight-line rate

DDB

Calculates depreciation using the double-declining balance method

MATCH

Finds the position of a value in a range

INDEX

Returns a value from a specified position in a table or range

Database functions

Functions that perform calculations on data organized like a database using criteria

IFERROR

Returns a value you specify if a formula results in an error otherwise returns the formula result