Excel 1-7 Flashcards
referencing a cell
Using the values stored in another cell of a worksheet
referencing a range
You can also refer to more than one cell at a time as long as the cells are in a continuous block
range
a group of cells in a continuous block
AutoFill
have Excel insert values into a range of cells by completing a pattern in previous cells
Quick sorting
an easy way to rearrange a data table, but it is limited to sorting just one column at a time
relative cell references
cell references that automatically update when a formula is pasted to a new cell
absolute cell reference
cell reference that remains constant when a formula is pasted into a new cell
To make a cell reference absolute
put a “$” character in front of the part of the reference that we wish to remain constant when a formula is pasted into a new cell
$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.
$A$1
Both the column and row references are absolute and will remain constant when the reference is copied and pasted to other cells.
function wizard
allows you to enter or select the inputs for the function, provides a description of the function and each argument, and lists the function result based on the inputs you enter
SUM function
the set of numbers you want to add together
COUNT
(value1, [value2], …) – counts the number of cells in a range of cells that contain numbers
COUNTA
(value1, [value2], …) – counts the number of cells in a range of cells that are not blank
AVERAGE
number1, [number2], …) – calculates the simple average of a set of numbers
MAX
(number1, [number2], …) – returns the largest value in a set of numbers
MIN
(number1, [number2], …) – returns the smallest value in a set of numbers
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.
EFFECT
(nominal_rate, npery) – calculates the annual percentage rate for an interest rate given the number of times per year that interest is charged.
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.
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
PV
(rate, nper, pmt, [fv], [type]) – calculates the current value (accounting for compounding interest) of an investment given the interest rate, number of payments to be made, and the amount of the payment.
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.
NPER
used to calculate the number of payments I will make on a loan before it is paid off.
PMT
used to calculate the payment amount for a loan
PV
used to calculate the current value of a loan or investment
FV
used to calculate the future value for a loan or investment after all of the payments are made or earned
RATE and EFFECT
help you understand how much interest you will be paying on a loan
Boolean logic
based on only two values 0 and 1. In Excel, the values 0 and 1 are represented by the values FALSE and TRUE, respectively
AND
AND (logical1,[logical2],…)
(1+1 = 2) TRUE. Combining these expressions with the operator AND (1+2 = 3) will result in TRUE because both of the combined expressions are TRUE.
(1+1 = 2) TRUE. Combining these expressions with the operator AND (1+2 = 4) will result in FALSE because the second expression is FALSE (even though the first expression is TRUE).
OR
OR (logical1,[logical2],…)
(1+1 = 2) TRUE. Combining these expressions with the operator OR (1+2 = 3) will result in TRUE
because both expressions are TRUE.
(1+1 = 2) TRUE. Combining these expressions with the operator OR (1+2 = 4) will result in TRUE
because the first expression is TRUE (even though the second expression is FALSE).
(1+1 = 1) FALSE. Combining these expressions with the operator OR (1+2 = 4) will result in FALSE
because both expressions are FALSE.
NOT
NOT(logical)
used to evaluate negation, or the opposite of a Boolean expression
IF
IF(logical_test,[value_if_true],[value_if_false])
COUNTIF
=COUNTIF(range,criteria)
allows you to determine the number of cells within a range of cells that contain a specific value
first argument of the COUNTIF function is the range of cells within which you want Excel to count the number of matches. The second argument defines the specific value that you would like Excel to match to the cells in the range
SUMIF
=SUMIF(range,criteria,[sum_range])
used to calculate the total for a set of values that match a specific criterion
first argument is the range of cells within which you want Excel to search for a match to the criterion you set. The second argument defines the criterion used to find matches
AVERAGEIF
=AVERAGEIF(range,criteria,[average_range])
used to calculate the average for a set of values that match a specific criterion
VLOOKUP
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
HLOOKUP
=HLOOKUP(lookup_value,table_array,row_index_number,[range_lookup])
VLOOKUP assumes that the first column of the reference table contains the values used to find a match for the “lookup_value”
TODAY
returns the current date
NOW
returns the current date and time formatted as a date and time.
DAY (serial_number)
returns the day portion of a date (a number between 1 and 31)
MONTH (serial_number)
returns the month portion of a date (a number between 1 and 12)
YEAR (serial_number)
returns the year portion of a date (a number between 1900 and 9999)
WEEKDAY (serial_number, [return_type])
returns the day of the week for a date
WEEKNUM (serial_number, [return_type])
returns the week of the year for a date
HOUR (serial_number)
returns the hour portion of a time as a number from 0 to 23
MINUTE (serial_number)
returns the minute portion of a time as a number from 0 to 59
SECOND (serial_number)
returns the second portion of a time as a number from 0 to 59
LEN (text)
returns the length, in number of characters, of a block of text
SEARCH (find_text, within_text, [start_num])
returns the position of a specific character, word, or phrase within a block of text
LEFT (text,[num_chars])
returns a specified number of characters starting from the beginning of a block of text
MID(text, start_num, num_chars)
returns a specified number of characters from the middle of a block of text
UPPER (text)
converts a block of text to all uppercase characters
LOWER (text)
converts a block of text to all lowercase characters
PROPER (text)
converts a block of text to title case (the first letter of each new word is capitalized)
CONCAT (text1,[text2],…)
combines blocks of text
SUBSTITUTE (text, old_text, new_text,[instance_num])
replaces specified characters, words, or phrases within a block of text with new characters, words, or phrases
Chart Title and Axis Titles
allow you to change where and how each of the titles in your chart will appear
Axes group
used to format the appearance of each of the axes in your chart
Column charts
used to compare different categories of items or the same category over time.
data series
set of data that you want to include on your chart.
Stacked Column Chart
used to compare categories of items or the same category over time
breaks the column bars into segments that represent subcategories that are consistent across the columns
Pie charts
used to compare the parts of a category to the whole
Typically, each of the parts is expressed as a percentage of the whole
line chart
compare the values of a particular category over time
spreadsheet model
generally used to represent the logic of a more complicated logical process
three common elements: changeable inputs, outputs, and intermediate calculations
changeable inputs
can be found in the customer information section
Model outputs
represent the outcome of the process that is modeled in the spreadsheet
