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
