ISTM EXCEL 2

F4

add $

SUM

add together #

COUNT

counts the number of cells in a range of cells that contain numbers

COUNTA

counts the number of cells in a range of cells that are not blank

AVERAGE

calculates the simple average of a set of numbers

MAX

returns the largest value in a set of numbers

MIN

returns the smallest value in a set of numbers

RATE

calculates the interest 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

calculates the annual percentage rate for an interest rate given the number of times per year that interest is charged.

NPER

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

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

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

calculates the future value of an investment given the interest rate, number of payments to be made, and the amount of the payment.

Trace Precedents

Under formula tab, arrows to boxes used in the formula

Trace Dependents

Under formula tab, arrows point to where this box is used in other boxes

TRUE

1, approximate match

FALSE

2, exact match

AND

function allows you to determine the number of cells within a range of cells that contain a specific value

All has to be true to return true

OR

function will take at least one Boolean expression (though like the AND function we usually combine at least two expressions) as arguments

True or False, boolean logic, only one has to be true to return true

NOT

is used to evaluate negation, or the opposite of a Boolean expression, returns opposite of true or false

IF

you know it

COUNTIF

function allows you to determine the number of cells within a range of cells that contain a specific value

SUMIF

function is used to calculate the total for a set of values that match a specific criterion

AVERAGEIF

function is used to calculate the average for a set of values that match a specific criterion

VLOOKUP/HLOOKUP

finds the appropriate match in the first column of the reference table, the desired information can be returned

date format

37250.31753

left of decimal - date

right of decimal - time

TODAY

returns the current date

NOW

returns the current date and time formatted as a date and time

DAY

returns the day portion of a date (a number between 1 and 31)

MONTH

returns the month portion of a date (a number between 1 and 12)

YEAR

returns the year portion of a date (a number between 1900 and 9999)

WEEKDAY

returns the day of the week for a date

WEEKNUM

returns the week of the year for a date

HOUR

returns the hour portion of a time as a number from 0 to 23

MINUTE

returns the minute portion of a time as a number from 0 to 59

SECOND

returns the second portion of a time as a number from 0 to 59

LEN

returns the length, in number of characters, of a block of text

SEARCH

returns the position of a specific character, word, or phrase within a block of text

LEFT

returns a specified number of characters starting from the beginning of a block of text

MID

returns a specified number of character from the middle of a block of text

RIGHT

returns a specified number of characters starting from the end of a block of text

UPPER

converts a block of text to all upper-case characters

LOWER

converts a block of text to all lower-case characters

PROPER

converts a block of text to title-case (the first letter of each new word is capitalized)

CONCATENATE

combines blocks of text

SUBSTITUTE

replaces specified characters, words, or phrases within a block of text with new characters, words, or phrases

Column Charts/Bar Charts

used to compare different categories of items or the same category over time

A good rule of thumb would be to limit the number of categories (or time intervals) examined to between five and ten

Bar Charts rotated 90 degrees

Pie Charts

used to compare the parts of category to the whole. Typically each of the parts is expressed as percentages of the whole

Line Charts

to compare the values of a particular category over time

Spreadsheet elements

changeable inputs, outputs, and intermediate calculations

What-if Analysis

scenario manager, goal seek, and data tables

Scenario Manager

useful for examining the impact of several inputs to a spreadsheet model changing at the same time

Goal Seek

used to work backwards from an outcome calculation to determine an input condition necessary to arrive at the desired outcome

Data tables

we can specify a number of different possible states for one or two input variables and construct a table in our worksheet that calculates an outcome for our model based on each state of the input variables.

PivotTables

used to organize and summarize large amounts of data

allows us to perform some fairly sophisticated analyses quickly and easily

Filters, Columns, Rows, and Values

CTLR A

select everything

CTLR .

toggle between corners