170 likes | 470 Vues
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
E N D
Day -9Excel chapter 7 NitinChowdary Are Nitin.are@mail.wvu.edu
Specialized functions • Math and statistical functions • Logical and lookup functions • Database functions • Financial functions
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)
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….)
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)
1. Nested logical functions • Nested IF within an IF function • Nest AND, OR, and NOT functions
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])
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)
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])
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)
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])