Day 10:Excel Chapter 7 Tazin AfrinTazin.Afrin@mail.wvu.edu February 11, 2014
Specialized functions • SUMIF • The SUMIF function calculates the total of a range of values when a specified condition is met. =SUMIF(range,criteria,sum_range) the range to sum that contains the data to sum range of cells you want to check against the criteria condition you want to match
Specialized functions • AVERAGEIF • The AVERAGEIF function calculates the average of values in a range when a specified condition is met. = AVERAGEIF(range,criteria,average_range) the range to sum that contains the data to sum range of cells you want to check against the criteria condition you want to match
Specialized functions • COUNTIF • The COUNTIF function counts the number of cells in a range when a specified condition is met. =COUNTIF(range,criteria) range of cells you want to check against the criteria condition you want to match
Specialized functions • Multiple Criterion • SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2,…) • AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2, …) • COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, …)
Relative standing • Rank • RANK.EQ: shared rank for ties • RANK.AVG: average rank for ties • PercentRank : identifies value’s rank as percentile • PERCENTRANK.INC: includes 0 and 1 • PERCENTRANK.EXC: excludes 0 and 1 • Quartile • QUARTILE.INC: includes top and bottom values • QUARTILE.EXC: excludes top and bottom values • Percentile • PERCENTILE.INC: includes the 0th and 100th percentiles • PERCENTILE.EXC: excludes the 0th and 100th percentiles
NESTED IF 75 IF(A1>=90, “A”, Z) >=90 N Y Z = IF(A1>=80, “B”, Y) >=80 A N Y Y = IF(A1>=70, “C”, X) >=70 B N Y X = IF(A1>=60, “D”, “F”) >=60 C N Y F D
LOGICAL FUNCTION =AND(logical1,logical2) • returnsTRUE when all arguments are true and FALSE when at least one argument is false. =OR(logical1,logical2) • returns TRUE if any argument is true and returns FALSE if all arguments are false. =NOT(logical) • returns TRUE if the argument is false and FALSE if the argument is true.
LOOKUP FUNCTION =MATCH(lookup_value,lookup_array,[match_type) • identifies a searched item’s position in a list. =INDEX(array,row_num,[column_num]) • returns a value or reference to a value within a range.
Database filtering and function • Advanced filtering • List range • Criteria range • Copy to • Functions =DSUM(database,field,criteria) =DAVERAGE(database,field,criteria) =DMAX(database,field,criteria) =DMIN(database,field,criteria) =DCOUNT(database,field,criteria)
Financial FUNCTION • Loan Amortization Table • a schedule showing monthly payments, interest per payment, amount toward paying off the loan, and the remaining balance for each payment. • Functions =IPMT(rate,per,nper,pv,[fv],[type]) =PPMT(rate,per,nper,pv,[fv],[type])
Financial FUNCTION =PV(rate,nper,pmt,[fv],[type]) • Calculates the present value of an investment. =NPER(rate,pmt,pv,[fv],[type]) • Calculates the number of periods for an investment or loan. =RATE(nper,pmt,pv,[fv],[type]) • Calculates the periodic rate for an investment or loan.
Day 10:Excel Chapter 8 Tazin AfrinTazin.Afrin@mail.wvu.edu February 11, 2014
What if analysis • What-if analysis is the process of changing variables to observe how changes affect calculated results. • A variable is a value that you can change to see how that change affects other values.
Goal seek • Goal Seek is a tool that identifies the necessary input value to obtain a desired goal. • Data-> Data tools group-> what-if analysis->Goal Seek
Scenario manager • Scenario Manager enables you to define and manage scenarios to compare how they affect results. • Data-> Data tools group-> what-if analysis->Scenario manager • Create • Edit • View • Summary report
Thank You Log Off
