OMIS-Excel Notes-Linda's

# Sumproduct sumproduct range a range b takes each

This preview shows page 1. Sign up to view the full content.

This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: ove formulas can take a variety of arguments instead of ranges; for example, individual values can also be input: (number1, number2, … ) For Finance • PMT(rate,nper,pv,[fv]) – calculates the payment for a loan based on constant payments and a constant interest rate – Rate is the interest rate for the loan – Nper is the number of repayment periods – Pv is the amount of the loan (present value) • Related formulas: – PV(rate,nper,pmt) – present value – FV(rate,nper,pmt) – future value Formulas and Calculations • Click on a cell to see how it is computed in box above spreadsheet • F2 function key – shows computation directly at cell; colour codes each input cell and highlights each with its own colour within the spreadsheet • Use Conditional Math and Statistical Functions • With Excel’s math and statistical functions, you can: – Perform conditional calculations – Indicate relative standing • When one condition must be met use: – SUMIF – AVERAGEIF – COUNTIF • The above functions require a: – Range argument to specify the range of cells to evaluate – Criteria argument that specifies the condition • The SUMIF and AVERAGEIF functions also contain an optional argument to specify the range of values to sum or average Excel contains math and statistical functions that enable you to calculate a statistic when a particular circumstance or set of circumstances exists. Additionally, you can rank individual values against others in a dataset. 14 Excel Notes Jessica Gahtan OMIS2000 SUMIF, AVERAGEIF, and COUNTIF perform calculations based on one condition being met. Calculate Relative Standing With Statistical Functions Excel contains four sets of statistical functions to indicate relative standing: – RANK.EQ and RANK.AVG calculate ranking for individual values within a list – PERCENTRANK.INC and PERCENTRANK.EXC calculate rank as a percentage for each...
View Full Document

## This note was uploaded on 03/28/2014 for the course OMIS 2000 taught by Professor Hk during the Fall '13 term at York University.

Ask a homework question - tutors are online