K201 Excel Lab Equation Notes PDF

Preview

Summary

Formulas needed for K201 Practical Exams ...

Description

K201 Excel Fact Sheet Cell referencing and formatting Relative cell addressing Both the column and row reference change when the formula or function is copied. Absolute cell addressing Both the column and row reference stay the same when the formula or function is copied. Mixed cell addressing Either the column or the row reference stays the same when the formula or function is copied. Conditional formatting Formatting appears in a cell when data in the cell meets specified conditions. Mathematical and statistical functions MIN(range) Finds the lowest (or earliest) value in a range. MAX(range) Finds the highest (or latest) value in a range. AVERAGE(range) Finds the average of the values in a range. COUNT(range) Counts the cells in a range that contain numeric data. COUNTA(range) Counts all non-empty cells in a range. COUNTIF(range, Counts the cells in a range that meet the criterion) criterion. SUM(range) Finds the sum of the values in a range. SUMIF(range, Calculates a sum based on a given criterion,[sum_range]) criterion. ROUND(number, Rounds a value (or cell reference, decimal_places) formula, or function) to the number of decimal places specified. INT(number) Rounds a number down to the nearest whole integer. Financial functions PMT(rate, nper, pv)

PPMT(rate,per,nper,pv)

IPMT(rate,per,nper,pv)

Given an interest rate (rate), number of payment periods (nper), and amount borrowed (pv), calculates the payment amount. Calculates the amount of principal paid in each individual payment period (per) of a loan. Calculates the amount of interest paid in each individual payment period (per) of a loan.

=B11*C10 =$B$11*$C$10 =$B11*C$10 Select Conditional Formatting on the Home tab. =MIN(B2:B17) =MAX(B2:B17) =AVERAGE(B2:B17) =COUNT(E12:E15) =COUNTA(C12:C15) =COUNTIF(D12:D15," >=90") =SUM(B2:B17) =SUMIF(A14:A34,”Yes” ,B14:B34) =ROUND(A4, 2) =INT(SUM(G11:G13))

=PMT(E16/12,F16*12,D16) =PMT(E16/4,F16*4,D16) =PPMT($E$16/12,A19,$ F$16*12,-$D$16) =IPMT($E$16/12,A19,$ F$16*12,-$D$16)

FV

Returns the future value of an investment based on periodic, constant payments and a constant interest rate.

=FV(rate,nper,pmt,[pv],[t ype])

PV

Returns the present value of an investment. The present value is the total amount that a series of future payments is worth now. For example, when you borrow money, the loan amount is the present value to the lender. Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate. Returns the interest rate per period of an investment.

=PV(rate, nper, -pmt, [fv], [type])

Returns the date from the computer’s clock as a serial number (which can be formatted to appear as a date). Returns the number of whole days, months, or years between two dates. Start_date must be earlier than end_date. Use “Y” for years, “M” for months, and “D” for days. To find weeks, first find days, then divide by 7. For whole weeks, also use INT.

=TODAY()

Evaluates a specified condition, performing one action if the condition is true and another action otherwise. Used when there are more than two outcomes.

=IF(C13>=124,"Y","N" )

NPER RATE Date functions TODAY()

DATEDIF(start_date, end_date, unit)

Logical functions IF(logical_test, value_if_true, value_if_false) Nested IF IF(lt, vt, IF(lt, vt, vf))

AND (test1, test2...) OR (test1, test2...)

Evaluates to TRUE when all logical tests are true. Evaluates to TRUE when at least one test is true.

Pivot Tables On the Insert tab, click PivotTable, then follow the steps in the wizard. Recommendation: Add the Values item last.

=NPER(rate,pmt,pv,[fv], [type]) =RATE(nper, pmt, pv, [fv], [type], [guess])

=DATEDIF(C11,D5,“Y ”) To calculate weeks: =INT(DATEDIF(E11, D5,“D”)/7)

If you have four outcomes: =IF(lt, vt, IF(lt, vt, IF(lt, vt, vf))) =IF(E12>=3.5,"High Honors", IF(E12>3.2,"Honor Student","Average")) =IF(AND(I18="A",H18...