1 / 16

Day - 9 Excel chapter 7

Day - 9 Excel chapter 7. Nitin Chowdary Are Nitin.are@mail.wvu.edu. Specialized functions Math and statistical functions Logical and lookup functions Database functions Financial functions. Math and statistical functions. 1. Conditional functions . SUMIF Function

diallo
Download Presentation

Day - 9 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 -9Excel chapter 7 NitinChowdary Are Nitin.are@mail.wvu.edu

  2. Specialized functions • Math and statistical functions • Logical and lookup functions • Database functions • Financial functions

  3. Math and statistical functions

  4. 1. Conditional functions • SUMIF Function • The SUMIF function calculates the total of a range of values when a specified condition is met =SUMIF(range,criteria,sum_range) • AVERAGEIF Function • The AVERAGEIF function calculates the average of values in a range when specific condition is met =AVERAGEIF(range,criteria,average_range) • COUNTIF Function • =COUNTIF(range,criteria)

  5. 1. Conditional functions • SUMIFS function • Calculates a total of a range of values when multiple conditions are met =SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2….) • AVERAGEIFS function • =AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2…) • COUNTIFS function • =COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2….)

  6. 2. Relative standing with statistical functions • RANK function • =RANK.EQ(number,ref,[order]) • =RANK.AVG(number,ref,[order]) • QUARTILE function • QUARTILE.INC function identifies the value at a specific quartile =QUARTILE.INC(array,quartile) • QUARTILE.EXC function identifies the values at a specific quartile, exclusive of 0 and 4 =QUARTILE.EXC(array,quartile)

  7. Logical and lookup functions

  8. 1. Nested logical functions • Nested IF within an IF function • Nest AND, OR, and NOT functions

  9. 2. Using match and index lookup functions • Finding position with Match function • The Match function identifies a searched items position in a list =MATCH(lookup_value,lookup_array,[match_type]) • Identifying details with Index Function • The Index Function returns a value or reference to a value with in a range =INDEX(array,row_num,[column_num])

  10. Database functions

  11. Manipulating data with database functions • Using DSUM and DAVERAGE functions • DSUM(database, field, criteria) • DAVERAGE(database, field, criteria) • Identify values with DMAX and DMIN • DMAX(database, field, criteria) • DMIN(database, field, criteria) • Identify the total number with DCOUNT • DCOUNT(database, field, criteria)

  12. FINANCIAL FUNCTIONS

  13. 1. Calculate interest and principal payments • IPMT function calculates periodic interest for a fixed term, fixed rate loan or investment • IPMT(rate,per,nper,pv,[fv],[type]) • PPMT function calculates the principal payment for a specifiepayment period given a fixed interest rate, term, and periodic payments. • PPMT(rate,per,nper,pv,[fv],[type])

  14. 2. Calculate cumulative interest and principal payments • CUMIPT function calculates cumulative interest for specified payment periods • CUMIPMT(rate,nper,pv,start_period,end_period) • CUMPRINC function calculates cumulative principal for specified payment periods • CUMPRINC(rate,nper,pv,start_period,end_period)

  15. 3. Calculate present and future values • PV function calculates the present value of an investment • PV(rate,nper,pmt,[fv],[type]) • FV function calculates the future value of an investment • FV(rate,nper,pmt,[pv],[type])

More Related