EXCEL Chapter 6 Quiz

basic counting functions

- COUNT - COUNTIF COUNTBLANK allow you to count ONLY data that meet specific criteria

AVERAGEIFS function

- take three required arguments: range, criteria range 1, and criteria - criteria 2 and criteria range 2 is optional - 127 max for criteria ranges and criteria - begins with the the cell range averaged - can evaluate a single criteria

COUNTIF

- takes two arguments: range and criteria criteria can be a text string, expression, or numerical value - can use wildcard characters to find inexact text matches. - Wildcard * replaces any string or characters - wildcard ? replaces a single character

AVERAGEIF function

- takes two required arguments: range and criteria - average range is optional (range of cells containing values to be averaged) - begins with the cell range to be evaluated against criteria

extra facts about TEXTJOIN

- when using a space in the delimiter in a TEXTJOIN function, if you type the formula directly into a cell or address bar, you must enclose the space in quotation marks. - if you use the function arguments dialog you can type the space in the Delimiter argument box

error messages

1. #N/A: a value or argument is missing 2. #VALUE: incorrect data type is used 3. #REF: a cell reference is empty 4. #DIV/0: the formula divides by zero or an empty cell 5. #NUM: the formula uses an invalid numeric entry 6. #NAME?: formula uses unrecognized text (misspelled function, sheet, or range name) 7. #NULL!: formula refers to an intersection of two cell ranges that do not interest, or uses an incorrect range separator

more info for database functions

1. expect the array to be organized like a database: must include label 2. large amounts of data: consider setting up a separate worksheet with criteria ranges

alternate way to perform statistical analysis

1. filtering data to meet criteria 2. using the regular statistical functions (using database version)

IFERROR function

A function that can determine if a cell contains an error value and then display the message you choose rather than the default error value requires two arguments: 1. value: the formula to calculate 2. value if error: the value, text, string, or formula to use if the formula results in an error - nested formula - easier to use this function through the formula bar or cell rather than the dialog box - can nest multiple IFERROR functions within each other - may contain alternate formula

and and or extra info

If the range you are summing is the same as the range you are evaluating, SUMIFS will allow you to use the same cell range in both arguments.

sum functions extra info

If the range you are summing is the same as the range you are evaluating, SUMIFS will allow you to use the same cell range in both arguments.

Time Value of Money

a dollar in hand today is worth more than a dollar promised at some time in the future - opportunity to invest that dollar - present value of future payments < sum of future payments

database functions

allow you to perform statistical analysis on data that meet specific criteria by building queries similar to those used when working with a database. - include: DSUM, DAVERAGE, DMIN, DMAX, DCOUNT and DCOUNTA - required arguments: database, field, and criteria

IFS Function

allows you to evaluate multiple logical tests in a single function

What does the AVERAGE function do?

calculate average value for specified criteria

Mean Absolute Deviation (MAD)

calculate how far the values in a population deviate from the average OR the average of the absolute differences between each value and the overall mean

future value (FV function)

calculate the future value of an investment - 3 required arguments: 1. interest rate: might need to be divided by 12, if annual 2. Nper: total number of payments 3. PMT: amount of each payment optional: 1. Pv: present value 2. Type: type in 1 if funds are added to the investment at the beginning of each period - =FV(Rate,Nper,Pmt,[Pv],[Type]) - if the PMT is negative, that means you are paying it out. If positive: bringing money in

criteria required

cell range defining the criteria the data must meet - criteria range must have at least two rows - can be numeric values, expressions, or text

CONCAT function

combine the text values and cells or cell ranges - CONCAT(B2 , " ", C2) - the space in the middle creates a one-space text strung between the values

TEXTJOIN function

concatenates cells using delimiter character to separate values in the new text string - TEXTJOIN(" ", TRUE, A2:C2)

INDEX function

find the value at the intersection of the column and row required arguments: 1. array: range of cells containing the entire data array 2. row number 3. column number

MATCH & INDEX function

first use the match to find the row or column of the value you want then use the results from the match function in the index function OR use the index function first, add the row and column arrays, select match function in name box, enter match arguments, click ok. - can apply to horizontal or vertical layout - horizontal layout: flip the row and column argument entering's

RANK.EQ function

if the data set includes duplicate values and you want the lowest ranking for that value use this function. - 2 required arguments: number (#, cell ref, or formula for which you want to find a ranking) and reference (cell range of the list within which the Number argument should be ranked) optional: order: tells Excel which way to sort the list to determine ranking

OR

if you are looking for a true value to any one of multiple conditions - returns TRUE if at least one argument is true and FALSE if all arguments are false

AND

if you are looking for a true value to multiple conditions - returns TRUE if all arguments are true or FALSE if one of the arguments is false

RANK.AVG function

if you want the average ranking for that value use this function - 2 required arguments: number (#, cell ref, or formula for which you want to find a ranking) and reference (cell range of the list within which the Number argument should be ranked) optional: order: tells Excel which way to sort the list to determine ranking (default sort is in descending order)

when you make changes to database functions...

make sure your criteria is updated in the dialog box

absolute number of a value

measures how far the number is from 0 - takes a single argument: Number: a value reference to a cell with a value for which you want to calculate the absolute value

standard deviation

measures how far values in a data set are spread out from the mean/average. - 2 types: Stdev.S or Stdev.P - Standard deviation is calculated by adding together the squared values of the deviation of each value from the mean (average), dividing by the total number of values, and then calculating the positive square root of the result.

logical function NOT

returns TRUE if the argument is false and FALSE if the argument is true - only evaluates one logical test HOW TO USE THE NOT FUNCTION: 1. select cell you want 2. click logical button and click NOT: this will open the dialog box 3. enter the expression to evaluate in logicalbox1

MATCH function

returns the position of a specific value in a single row or column array required arguments: 1. lookup value: text, #, or logical value you want to match 2. lookup array: range of cells (grouped in a row or column) where the value is 3. optional: match type: - can enter 0 to find exact match - one to use smallest to largest to find first position where the value is less than or equal to the lookup value. - negative one is for largest to smallest instead and greater than or equal to the lookup value

Stdev.s

set of values based on a sample of the population

NPV function

similar to pv function - allows for variable payment amounts when calculating the present value of future payments - useful for comparing two investment payment options 2 required arguments: 1. discount rate 2. value: the payment amount both incoming and outgoing (takes up to 254 values) - assumes payments are made over consistent periods and at the end of the pay period - NPV ignores cells with text or blank cells (enter 0 instead of leaving blank cells) =NPV(Rate,Value, Value 2, etc) - the value of today of future payments minus the cost of investment

Stdev.p

uses entire set of values as an argument

field required

the column to use in the calculation

database required

the data arranged in rows and columns with a column label

ranking values

the sort order of a value relative to all the other values in the data set

concatenate

to link items together

SUMIFS Function

to sum data that meets multiple criteria - up to 127 pairs of criteria and criteria ranges - requires three arguments: sum range, criteria range 1, criteria 1, optional: criteria range 2 and criteria 2 - begins with the cell range to be summed - can use to evaluate a single criteria too - If the range you are summing is the same as the range you are evaluating, SUMIFS will allow you to use the same cell range in both arguments.

SUMIF Function

totals the values only where the cell meets the specified criteria - takes two required arguments: range, criteria, and an optional sum range - begins with the cell range to be evaluated against the criteria

present value

value today of a series of future payments - PV function: use when payments are constant 3 required arguments: 1. interest rate: (is annual, needs to be divided by # of pay periods) 2. Nper: total number of payments 3. PMT: the amount of each payment optional: 1. FV: the amount of money left after the last payment is made 2. Type: enter 1 if the payment is made at the beginning of each period