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.

MAS TERING EX CE L ? F UNCT I ONS & F ORM UL AS

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

? The 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

P A R T I C I P A N T N OTEBOOK

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

MAS TERING EX CE L ? F UNCT I ONS & F ORM UL AS

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

P A R T I C I P A N T N OTEBOOK

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.

Google Online Preview   Download