1 / 12

Day 10: Excel Chapter 7

Day 10: Excel Chapter 7. Tazin Afrin Tazin.Afrin@mail.wvu.edu September 19, 2013. Relative standing. Rank Identifies a value’s rank within a list of value =RANK.EQ( number,ref ,[order]) =RANK.AVG( number,ref ,[order])

arvid
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 September 19, 2013

  2. Relative standing • Rank • Identifies a value’s rank within a list of value =RANK.EQ(number,ref,[order]) =RANK.AVG(number,ref,[order]) • identifies the rank of a value but assigns an average rank when identical values exist.

  3. Relative standing • PercentRank • Displays a value’s rank as a percentile of the range of data in the dataset. • The first rank is 1.000 • rank of the highest value • The lowest percent rank is 0.000 • rank of the lowest value =PERCENTRANK.INC(array,x,[significance]) • includes 0 and 1 =PERCENTRANK.EXC(array,x,[significance]) • excludes 0 and 1

  4. Relative standing • Quartile • Identifies a value at a specific quartile for a dataset • quartile 0 for the lowest value • quartile 4 for the highest value =QUARTILE.INC(array,quart) • includes top and bottom values =QUARTILE.EXC(array,quart) • excludes top and bottom values

  5. Relative standing • Percentile • identifies the kth percentile of a specified value within a list of values =PERCENTILE.INC(array,k) • includes the 0th and 100th percentiles =PERCENTILE.EXC(array,k) • excludes the 0th and 100th percentiles

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

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

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

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

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

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

  12. Thank You Log Off

More Related