1 / 17

Day 10: Excel Chapter 7

Day 10: Excel Chapter 7. Tazin Afrin Tazin.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.

salali
Download Presentation

Day 10: Excel Chapter 7

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Day 10:Excel Chapter 7 Tazin AfrinTazin.Afrin@mail.wvu.edu February 11, 2014

  2. 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

  3. 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

  4. 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

  5. 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, …)

  6. 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

  7. 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

  8. 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.

  9. 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.

  10. 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)

  11. 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])

  12. 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.

  13. Day 10:Excel Chapter 8 Tazin AfrinTazin.Afrin@mail.wvu.edu February 11, 2014

  14. 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.

  15. 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

  16. 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

  17. Thank You Log Off

More Related