day 10: excel chapter 7 tazin afrin [email protected] [email protected] september 19,...
TRANSCRIPT
![Page 2: DAY 10: EXCEL CHAPTER 7 Tazin Afrin Tazin.Afrin@mail.wvu.edu Tazin.Afrin@mail.wvu.edu September 19, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649e415503460f94b33a21/html5/thumbnails/2.jpg)
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.
![Page 3: DAY 10: EXCEL CHAPTER 7 Tazin Afrin Tazin.Afrin@mail.wvu.edu Tazin.Afrin@mail.wvu.edu September 19, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649e415503460f94b33a21/html5/thumbnails/3.jpg)
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
![Page 4: DAY 10: EXCEL CHAPTER 7 Tazin Afrin Tazin.Afrin@mail.wvu.edu Tazin.Afrin@mail.wvu.edu September 19, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649e415503460f94b33a21/html5/thumbnails/4.jpg)
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
![Page 5: DAY 10: EXCEL CHAPTER 7 Tazin Afrin Tazin.Afrin@mail.wvu.edu Tazin.Afrin@mail.wvu.edu September 19, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649e415503460f94b33a21/html5/thumbnails/5.jpg)
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
![Page 6: DAY 10: EXCEL CHAPTER 7 Tazin Afrin Tazin.Afrin@mail.wvu.edu Tazin.Afrin@mail.wvu.edu September 19, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649e415503460f94b33a21/html5/thumbnails/6.jpg)
6
NESTED IF
>=90
>=80
>=70
>=60
A
B
C
DF
X = IF(A1>=60, “D”, “F”)
Y = IF(A1>=70, “C”, X)
Z = IF(A1>=80, “B”, Y)
IF(A1>=90, “A”, Z)
75
N Y
N Y
N Y
N Y
![Page 7: DAY 10: EXCEL CHAPTER 7 Tazin Afrin Tazin.Afrin@mail.wvu.edu Tazin.Afrin@mail.wvu.edu September 19, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649e415503460f94b33a21/html5/thumbnails/7.jpg)
7
LOGICAL FUNCTION
=AND(logical1,logical2) – returns TRUE 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.
![Page 8: DAY 10: EXCEL CHAPTER 7 Tazin Afrin Tazin.Afrin@mail.wvu.edu Tazin.Afrin@mail.wvu.edu September 19, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649e415503460f94b33a21/html5/thumbnails/8.jpg)
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.
![Page 9: DAY 10: EXCEL CHAPTER 7 Tazin Afrin Tazin.Afrin@mail.wvu.edu Tazin.Afrin@mail.wvu.edu September 19, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649e415503460f94b33a21/html5/thumbnails/9.jpg)
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)
![Page 10: DAY 10: EXCEL CHAPTER 7 Tazin Afrin Tazin.Afrin@mail.wvu.edu Tazin.Afrin@mail.wvu.edu September 19, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649e415503460f94b33a21/html5/thumbnails/10.jpg)
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])
![Page 11: DAY 10: EXCEL CHAPTER 7 Tazin Afrin Tazin.Afrin@mail.wvu.edu Tazin.Afrin@mail.wvu.edu September 19, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649e415503460f94b33a21/html5/thumbnails/11.jpg)
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.
![Page 12: DAY 10: EXCEL CHAPTER 7 Tazin Afrin Tazin.Afrin@mail.wvu.edu Tazin.Afrin@mail.wvu.edu September 19, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649e415503460f94b33a21/html5/thumbnails/12.jpg)
THANK YOU LOG OFF