Mastering Excel
Excel Mastering ?
Functions & Formulas
? 2016 SkillPath a division of the Graceland College Center for Professional Development and Lifelong Learning, Inc. All rights reserved, including the right to reproduce this material or any part thereof in any manner.
MASTERING EXCEL? FUNCTIONS & FORMULAS
Financial and Statistical Function Overview
Financial and Statistical Function Overview
? Financial: PMT, FV, and RATE functions ? Statistical: AVERAGE, MAX, MIN, MODE, MEDIAN ? Others: STDEV, VAR, PERCENTILE, QUARTILE, RANK, LARGE
The IF Function
? =IF(Logical_Test,[value_if_true],[value_if_false]) Conditional operators: < > = >= In this example, commission will be paid if "Profit" exceeds 35% of the sale.
? =IF(D7>B7*0.35,B7*0.1,"No Bonus")
The Nested IF Function
? =IF(Logical_Test,[value_if_true],[value_if_false]) T he logic is the same for both IF and Nested IF. In this example, commission levels are paid at 10% if the profit is greater than 35% of the sale and 5% if the profit is greater than 25% of the sale.
? =IF(D7>B7*0.35,B7*0.1,IF(D7>B7*0.25,B7*0.05,0))
WTEXF030116
2
PARTICIPANT NOTEBOOK
Using IF -- AND
? =IF(AND(condition1,condition2,...),true,false) Exp. paying commission on sales made in the "Central" region AND "Profit" exceeds 25% of the sale
? =IF(AND(D7>B7*0.25,C7="Central"),B7*0.05,0)
Using IF -- OR
? =IF(OR(condition1,condition2,...),true,false) Exp. paying commission on sales made in the "Central" region OR "Profit" exceeds 50% of the sale
? =IF(OR(D7>B7*0.5,C7="Central"),B7*0.05,0)
3
WTEXF030116
MASTERING EXCEL? FUNCTIONS & FORMULAS
CHOOSE
The CHOOSE function allows you to insert data into a cell based on a table rather than having to manually insert the data. The structure of the CHOOSE function is: CHOOSE(index, value1, value2, value3...) In this example, we are using CHOOSE to determine what level of award the salespeople receive based on their rank. This prevents us from having to manually determine the awards.
WTEXF030116
4
PARTICIPANT NOTEBOOK
VLOOKUP
VLOOKUP allows the user to maintain a table that contains pertinent data that can be referenced to "grab" desired information instead of having to manually look up information. VLOOKUPs are great for inventory, employee data, student data, etc. All of the data can be maintained in a list, and as the list changes the tables that point to the table change also. This is good when using VLOOKUP for inventory or employee data. This example is a good use of a VLOOKUP. In the example, we want to find the price of the part. We will use a VLOOKUP to locate the price and insert it into the table.
Note: VLOOKUP tables should be absolute so that when the formula is copied to other cells, they continue to reference the VLOOKUP table. The best way to guarantee the VLOOKUP table is absolute is to name the range that contains the table.
5
WTEXF030116
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.