FREQUENTLY USED SHORTCUTS

Navigation
Cell Navigation and Selection
Move one cell in any direction Arrow Keys
Go to end of contiguous range CTRL + Arrow Keys
Select range SHIFT + Arrow Keys
Select contiguous range CTRL + SHIFT + Arrow Keys
Move one page up (down) Page Up (Down)
Move to beginning of row Home
Move to cell "A1" CTRL + Home
Move to cell to the right (left) TAB (SHIFT + TAB)
Move to cell below (above) Enter (SHIFT + Enter)
Select All CTRL + A

Worksheet Navigation
Move to next/previous worksheet CTRL + Page Up/Down
New Worksheet SHIFT + F11

Workbook Navigation
Switch workbooks CTRL + TAB
Switch workbooks in reverse CTRL + SHIFT + TAB
New Workbook CTRL + N

Basics
Save CTRL + S
Print CTRL + P
New Workbook CTRL + N
Close Program ALT + F4
Open Workbook CTRL + O
Close Window CTRL + W (CTRL + F4)

Recalculate Workbook F9

Formatting
Cell Formatting
Format Menu CTRL + 1
Bold CTRL + B
Insert a comment SHIFT + F2
Editing
Cells
Edit Cell F2
Cancel cell entry ESC
Anchor cells F4
Highlight within cells SHIFT + Arrow Keys

Fill Down CTRL + D

Fill Right CTRL + R

Copy CTRL + C
Paste CTRL + V
Paste Special CTRL + ALT + V
Cut CTRL + X
Undo CTRL + Z
Repeat CTRL + Y

Row/Columns
Select Column CTRL + SPACE
Select Row SHIFT + SPACE
Delete Row or Column CTRL + "-"
Insert Row or Column CTRL + "+"
Group/Ungroup rows or columns ALT + SHIFT + Left/Right Arrow Keys

Tools/Other Features
Display Name Menu CTRL + F3
Go To F5 or CTRL + G
Find CTRL + F
Replace CTRL + H

Auditing
Show Formulas CTRL + ~
Go To F5 or CTRL + G
 ALT Shortcuts
Cell Size
Row Height ALT + H + O + H
AutoFit Row Height ALT + H + O + A
Column Width ALT + H + O + W
AutoFit Column Width ALT + H + O + I
Default Column Width ALT + H + O + D

Visibility
Hide/Unhide Columns, Rows & Sheets ALT + H + O + U

Organize Sheets
Rename Sheet ALT + H + O + R
Move or Copy Sheet ALT + H + O + M
Tab Color ALT + H + O + T

Viewing
Freeze Panes ALT + W + F + F
Split Screen ALT + W + S

Arrow Keys

Tools
Sort ALT + A + SS
Filter ALT + A + T
Text to Columns ALT + A + E
Data Validation ALT + A + V

Auditing
Trace Precedents ALT + M + P
Trace Dependents ALT + M + D
Remove Trace Arrows ALT + M + A + A
Track Changes ALT + R + G
Fixing Error

Message Cause
####### The information does not fit in the column
#NAME? The name of the cell or function is misspelled.
#REF! A cell in the formula does not exist or the name is spelled wrong.
#VALUE! A cell in the formula contains text rather than a numerical value.
#DIV/0! The denominator of the formula is zero.
Fix The Error
Solve by widening the cell.
Open the formula and correct the cell or function name.
Open the formula and correct the cell reference.
Remove the referenced cell from the formula or adjust the cell contents so it contains a number.
Solve using an IF statement so the calculation is only done IF the denominator is greater than zero.
Exercise
Tax Rate 30%
City Jan Feb Mar Total Average Tax
Delhi ₹ 50,038.0 ₹ 33,222.0 ₹ 16,406.0 #VALUE! #NAME? #REF!
Mumbai ₹ 55,472.0 ₹ 36,554.0 ₹ 17,636.0 #VALUE! #NAME? #REF!
Kolkata ₹ 6,566.0 ₹ 6,566.0 ₹ 6,566.0 #VALUE! #NAME? #REF!
Chennai ₹ 33,736.0 ₹ 23,226.0 ₹ 12,716.0 #VALUE! #NAME? #REF!
Hyderabad ₹ 1,132.0 ₹ 3,234.0 ₹ 5,336.0 #VALUE! #NAME? #REF!
Bangalore ₹ 12,000.0 ₹ 9,898.0 ₹ 7,796.0 #VALUE! #NAME? #REF!
Ahmedabad ₹ 22,868.0 ₹ 16,562.0 ₹ 10,256.0 #VALUE! #NAME? #REF!
Pune ₹ 39,170.0 ₹ 26,558.0 ₹ 13,946.0 #VALUE! #NAME? #REF!
Kanpur ₹ 28,302.0 ₹ 19,894.0 ₹ 11,486.0 #VALUE! #NAME? #REF!
Coimbatore ₹ 17,434.0 ₹ 13,230.0 ₹ 9,026.0 #VALUE! #NAME? #REF!
Indore ₹ 44,604.0 ₹ 29,890.0 ₹ 15,176.0 #VALUE! #NAME? #REF!
BODMAS - Brackets, Order/Index (or Power), Division, Multiplication, Addition & Subtraction

Brackets 2+3*4 14 20
=2+3*4 =(2+3)*4

Order/Index (or Power) 1 + 2 ^ 3 -2 7 25

=1+2^3-2 =(1+2)^3-2

Division 2+6/2 5 4
=2+6/2 =(2+6)/2
SUMPRODUCT

Item Sold price

Tyres 5 100
Filters 2 10
Bulbs 3 2

Total Sales Value : 526 =SUMPRODUCT(D4:D6,E4:E6)

What Does It Do ?
This function uses at least two columns of values.
The values in the first column are multipled with the corresponding value in the second column.
The total of all the values is the result of the calculation.

Syntax
=SUMPRODUCT(Range1, Range, Range3 through to Range30)

Formatting
No special formatting is needed.

Example
The following table was used by a drinks merchant to keep track of stock.
The merchant needed to know the total purchase value of the stock, and the potential
value of the stock when it is sold, takinging into account the markup percentage.

The =SUMPRODUCT() function is used to multiply the Cases In Stock with the Case Price to
calculate what the merchant spent in buying the stock.

The =SUMPRODUCT() function is used to multiply the Cases In Stock with

the Bottles In Case and the Bottle Setting Price, to calculate the potential value of the
stock if it is all sold.

Cases In Bottles Bottle Selling

Product Stock Case Price In Case Bottle Cost Markup Price
Red Wine 10.00 120.00 10.00 12.00 0.25 15.00
White Wine 8.00 130.00 10.00 13.00 0.25 16.25
Champagne 5.00 200.00 6.00 33.33 0.80 60.00
Beer 50.00 24.00 12.00 2.00 0.20 2.40
Lager 100.00 30.00 12.00 2.50 0.25 3.13
=D39/E39 =F39+F39*G39

Total Value Of Stock : £7,440 =SUMPRODUCT(C35:C39,D35:D39)

Total Selling Price Of Stock : £9,790 =SUMPRODUCT(C35:C39,E35:E39,H35:H39)

Profit : £2,350 =E44-E43

Exercise

Calculate the weighted average score for the below course.

Course Items Weight Score

Assignment 1 15.0% 20.00
Assignment 2 15.0% 20.00
Assginment 3 20.0% 20.00
Exam 50.0% 40.00

Final Score

Exercise Calculate the weighted average share price based on probability weights.

Method Probability Share Price

Management Estimate 40.0% 28.00
Analyst Estimate 20.0% 35.00
Broker Estimate 10.0% 39.00
Investor Estimate 30.0% 40.00

Share Price Estimate

SUMIF AND COUNTIF

SUMIF
What Does It Do ?
This function adds the value of items which match criteria set by the user.

Syntax
=SUMIF(Range Of Things To Be Examined, Criteria To Be Matched, Range Of Values To Total)

COUNTIF
What Does It Do ?
This function counts the number of items which match criteria set by the user.

Syntax
=COUNTIF(Range Of Things To Be Counted, Criteria To Be Matched)

Example: COUNTIF

Item Date Cost

Brakes 35796 80
Tyres 35925 25
Brakes 35827 80
Service 35855 150
Service 35800 300
Window 35947 50
Tyres 35886 200
Tyres 35855 100
Clutch 35916 250

How many Brake Shoes Have been bought. 2 =COUNTIF(A20:A28,"Brakes")

How many Tyres have been bought. 3 =COUNTIF(A20:A28,"Tyres")
How many items cost £100 or above. 5 =COUNTIF(C20:C28,">=100")
Type the name of the item to count. tyres =COUNTIF(A20:A28,B33)

Calculate total cost of Brakes 160 =SUMIF(A20:A28,"Brakes",C20:C28)

Calculate total cost of tyres 325 =SUMIF(A20:A28,"Tyres",C20:C28)
 Exercise

Company Name Industry Classification Market Capitalization

Wipro Limited IT Consulting and Other Services 22482.7
Tata Consultancy Services Limited IT Consulting and Other Services 73461
Tech Mahindra Limited IT Consulting and Other Services 6343.2
HCL Technologies Limited IT Consulting and Other Services 19187
Dabur India Ltd Household Products 8636.77
Godrej Consumer Products Ltd Household Products 9926.86
Jyothy Laboratories Ltd Household Products 1088.94
Colgate-Palmolive (India) Ltd Household Products 4783.04

Market Size

No. of Firms

IT Consulting and Other Services

Household Products

Brakes",C20:C28)
Tyres",C20:C28)
Calculate total market cap for the companies having industry classification as Household Products

Count Number of firms having Industry Classification as IT Consulting and Other Services
Basic logical statements
Exercise Exercise
Score 80 NOT 80 >80 >=80 Color Green NOT green
69 Blue
81 Red
78 Green
90 Yellow
85 Orange
80 Gray
AND

Items To Test Result

500 800 1 =AND(C4>=100,D4>=100)
500 25 0 =AND(C5>=100,D5>=100)
25 500 0 =AND(C6>=100,D6>=100)
12 1 =AND(D7>=1,D7<=52)

What Does It Do?

This function tests two or more conditions to see if they are all true.
It can be used to test that a series of numbers meet certain conditions.
It can be used to test that a number or a date falls between an upper and lower limit.
Normally the AND() function would be used in conjunction with a function such as =IF().

Syntax
=AND(Test1,Test2)
Note that there can be up to 30 possible tests.

Formatting
When used by itself it will show TRUE or FALSE.

Example 1
The following example shows a list of examination results.
The teacher wants to find the pupils who scored above average in all three exams.
The =AND() function has been used to test that each score is above the average.
The result of TRUE is shown for pupils who have scored above average in all three exams.

Name Maths English Physics Passed

Alan 80 75 85 TRUE
Bob 50 30 40 FALSE
Carol 60 70 50 FALSE
David 90 85 95 TRUE
Eric 20 30 Absent FALSE
Fred 40 60 80 FALSE
Gail 10 90 80 FALSE
Harry 80 70 60 TRUE
Ian 30 10 20 FALSE
Janice 10 20 30 FALSE
=AND(C38>=AVERAGE($C$29:$C$38),D38>=AVERAGE($D$29:$D$38),E38>=AVERAGE($E$29:$E$38))

Averages 47 54 60
 Exercise
Use AND to join statements

Score >75 AND <90

69
81
78
90
85
80

Exercise
Passing Score 75
Phone Cost 25,000

Score Savings Parent's Gift Phone Budget

45 10,000 30,000
50 28,000 40,000
60 30,000 35,000
80 5,000 35,000
90 28,000 22,000
100 35,000 25,000

E$29:$E$38))
If passed AND if personal savings is less than phone cost then budget is
parent's gift savings. Else budget is personal savings.
Color Qty Red OR green AND > 10
Blue 8
Red 12
Green 16
Red 8
Blue 12
Green 18
OR

Order No. Cost Payment Type Handling Charge

AB001 1,000 Cash - =IF(OR(E4="Visa",E4="Delta"),5,0)
AB002 1,000 Visa 5 =IF(OR(E5="Visa",E5="Delta"),5,0)
AB003 2,000 Cheque - =IF(OR(E6="Visa",E6="Delta"),5,0)
AB004 5,000 Delta 5 =IF(OR(E7="Visa",E7="Delta"),5,0)

What Does It Do?

This function tests two or more conditions to see if any of them are true.
It can be used to test that at least one of a series of numbers meets certain conditions.
Normally the OR() function would be used in conjunction with a function such as =IF().

Syntax
=OR(Test1,Test2)
Note that there can be up to 30 possible tests.

Formatting
When used by itself it will show TRUE or FALSE.

Example
The following table shows a list of orders taken by a company.
A handling charge of £5 is made on all orders paid by Visa or Delta cards.
The =OR() function has been used to determine whether the charge needs to be applied.

Order No. Cost Payment Type Handling Charge

AB001 1,000 Cash - =IF(OR(E27="Visa",E27="Delta"),5,0)
AB002 1,000 Visa 5
AB003 2,000 Cheque -
AB004 5,000 Delta 5
 Exercise

4="Visa",E4="Delta"),5,0) Color Green OR Red

5="Visa",E5="Delta"),5,0) Blue
6="Visa",E6="Delta"),5,0) Red
7="Visa",E7="Delta"),5,0) Green
Red
Blue
Green

Exercise

Passing Score 75
Phone Cost 25,000

Score Savings Parent's Gift Phone Budget

45 10,000 30,000
50 28,000 40,000
60 30,000 35,000
80 5,000 35,000
90 28,000 22,000
27="Visa",E27="Delta"),5,0) 100 35,000 25,000
If failed OR savings is greater than equal to phone cost then budget is personal savings. Else budget is parent's gift.
s parent's gift.
IF

Name Sales Target Result

Alan 1,000 5,000 Not Achieved =IF(C4>=D4,"Achieved","Not Achieved")
Bob 6,000 5,000 Achieved =IF(C5>=D5,"Achieved","Not Achieved")
Carol 2,000 4,000 Not Achieved =IF(C6>=D6,"Achieved","Not Achieved")

What Does It Do?

This function tests a condition.
If the condition is met it is considered to be TRUE.
If the condition is not met it is considered as FALSE.
Depending upon the result, one of two actions will be carried out.

Syntax
=IF(Condition,ActionIfTrue,ActionIfFalse)
The Condition is usually a test of two cells, such as A1=A2.
The ActionIfTrue and ActionIfFalse can be numbers, text or calculations.

Formatting
No special formatting is required.

Example 1
The following table shows the Sales figures and Targets for sales reps.
Each has their own target which they must reach.
The =IF() function is used to compare the Sales with the Target.
If the Sales are greater than or equal to the Target the result of Achieved is shown.
If the Sales do not reach the target the result of Not Achieved is shown.
Note that the text used in the =IF() function needs to be placed in double quotes "Achieved".

Name Sales Target Result

Alan 1,000 5,000 Not Achieved =IF(C31>=D31,"Achieved","Not Achieved")
Bob 6,000 5,000 Achieved =IF(C32>=D32,"Achieved","Not Achieved")
Carol 2,000 4,000 Not Achieved =IF(C33>=D33,"Achieved","Not Achieved")

Example 2
The following table is similar to that in Example 1.
This time the Commission to be paid to the sales rep is calculated.
If the Sales are greater than or equal to the Target, the Commission is 10% of Sales.
If the Sales do not reach Target, the Commission is only 5% of Sales.

Name Sales Target Commission

Alan 1,000 5,000 50 =IF(C43>=D43,C43*10%,C43*5%)
Bob 6,000 5,000 600 =IF(C44>=D44,C44*10%,C44*5%)
Carol 2,000 4,000 100 =IF(C45>=D45,C45*10%,C45*5%)
Example 3
This example uses the =AND() within the =IF() function.
A builders merchant gives 10% discount on certain product lines.
The discount is only given on products which are on Special Offer, when the Order Value
is £1000 or above.
The =AND() function is used with the =IF() to check that the product is on offer and that
the value of the order is above £1000.

Special Order
Product Offer Value Discount Total
Wood Yes 2,000 200 1,800
Glass No 2,000 - 2,000
Cement Yes 500 - 500
Turf Yes 3,000 300 2,700
=IF(AND(C61="Yes",D61>=1000),D61*10%,0)
Exercise

Passing Score 75 If student passes then phone budget is parent's gift else

Score Savings Parent's Gift Phone Budget

45 10,000 30,000
50 28,000 40,000
60 30,000 35,000
80 5,000 35,000
90 18,000 22,000
100 20,000 25,000

Exercise
If Gender is Male, Then add Mr. to Name else add Ms. To Name
Name Gender Mr/Ms
Arun M
Prabhu M
Sandhya F
Geetha F
Mahesh M
Priya F
Krishna M
e budget is parent's gift else the budget is personal savings.
Excel Function Dictionary VLOOKUP
© 1998 - 2000 Peter Noneley Page 30 of 44

A B C D E F G H I J K L M N O P Q R
1 VLOOKUP
2
3 The column numbers are not needed.
4 they are part of the illustration. Exercise
5 col 1 col 2 col 3 col 4 col 5 col 6
6 Jan 10 20 30 40 50 Expenses
7 Feb 80 90 100 110 120 Name Walter Jesse Saul Hank Gustavo
8 Mar 97 69 45 51 77 Chemicals 80,000 100,000 72,000 64,000 96,000
9 Equipment 50,000 62,500 45,000 40,000 60,000
10 Toys 15,000 18,750 13,500 12,000 18,000
11 Type a month to look for : Feb Powder 70,000 87,500 63,000 56,000 84,000
12 Which column needs to be picked out : 4 Cars 20,000 25,000 18,000 16,000 24,000
13 Apparel 8,000 10,000 7,200 6,400 9,600
14 The result is : 100 Food 2,000 2,500 1,800 1,600 2,400
15 =VLOOKUP(G11,C6:H8,G12,FALSE)
16
17 What Does It Do ?
18 This function scans down the row headings at the side of a table to find a specified item. VLOOKUP
19 When the item is found, it then scans across to pick a cell entry. Lookup below expenses for Saul
20 Toys
21 Syntax Powder
22 =VLOOKUP(ItemToFind,RangeToLookIn,ColumnToPickFrom,SortedOrUnsorted) Apparel
23 The ItemToFind is a single item specified by the user.
24 The RangeToLookIn is the range of data with the row headings at the left hand side.
25 The ColumnToPickFrom is how far across the table the function should look to pick from.
26 The Sorted/Unsorted is whether the column headings are sorted. TRUE for yes, FALSE for no.
27 VLOOKUP to calculate commission with approximate match
28 Formatting
29 No special formatting is needed. Name Sales Comm % Commission Sales Comm %
30 Applebee 171,900 - 50,000 3%
31 Example 1 Bueller 93,500 - 75,000 4%
32 This table is used to find a value based on a specified name and month. Chung 151,200 - 100,000 5%
33 The =VLOOKUP() is used to scan down to find the name. Crawford 119,850 - 125,000 6%
34 The problem arises when we need to scan across to find the month column. Joyce 89,450 - 175,000 8%
35 To solve the problem the =MATCH() function is used. King 124,500 - 200,000 9%
36 MacDonald 131,100 -
37 The =MATCH() looks through the list of names to find the month we require. It then calculates Richards 55,300 -
38 the position of the month in the list. Unfortunately, because the list of months is not as wide Tanaka 201,500 -
39 as the lookup range, the =MATCH() number is 1 less than we require, so and extra 1 is
40 added to compensate.
41
42 The =VLOOKUP() now uses this =MATCH() number to look across the columns and
43 picks out the correct cell entry.
44
45 The =VLOOKUP() uses FALSE at the end of the function to indicate to Excel that the
46 row headings are not sorted.
47
48
49 Jan Feb Mar
50 Bob 10 80 97
51 Eric 20 90 69
52 Alan 30 100 45
53 Carol 40 110 51
54 David 50 120 77
55
56 Type a name to look for : eric
57 Type a month to look for : mar
58
59 The result is : 69
60 =VLOOKUP(F56,C50:F54,MATCH(F57,D49:F49,0)+1,FALSE)
61
62 Example 2
63 This example shows how the =VLOOKUP() is used to pick the cost of a spare part for
64 different makes of cars.
65 The =VLOOKUP() scans down row headings in column F for the spare part entered in column C.
66 When the make is found, the =VLOOKUP() then scans across to find the price, using the
67 result of the =MATCH() function to find the position of the make of car.
68
69 The functions use the absolute ranges indicated by the dollar symbol . This ensures that
70 when the formula is copied to more cells, the ranges for =VLOOKUP() and =MATCH() do
71 not change.
72
73 Maker Spare Cost Lookup Table
74 Vauxhall Ignition 50 Vauxhall Ford VW
75 VW GearBox 600 GearBox 500 450 600
76 Ford Engine 1,200 Engine 1,000 1,200 800
77 VW Steering 275 Steering 250 350 275
78 Ford Ignition 70 Ignition 50 70 45
79 Ford CYHead 290 CYHead 300 290 310
80 Vauxhall GearBox 500
81 Ford Engine 1,200
82 =VLOOKUP(C81,F75:I79,MATCH(B81,G74:I74,0)+1,FALSE)
83
84
85 Example 3
86 In the following example a builders merchant is offering discount on large orders.
87 The Unit Cost Table holds the cost of 1 unit of Brick, Wood and Glass.
88 The Discount Table holds the various discounts for different quantities of each product.
89 The Orders Table is used to enter the orders and calculate the Total.
90
91 All the calculations take place in the Orders Table.
92 The name of the Item is typed in column C of the Orders Table.
93
94 The Unit Cost of the item is then looked up in the Unit Cost Table.
95 The FALSE option has been used at the end of the function to indicate that the product
96 names down the side of the Unit Cost Table are not sorted.
97 Using the FALSE option forces the function to search for an exact match. If a match is
98 not found, the function will produce an error.
99 =VLOOKUP(C126,C114:D116,2,FALSE)
100
101 The discount is then looked up in the Discount Table
102 If the Quantity Ordered matches a value at the side of the Discount Table the =VLOOKUP will
103 look across to find the correct discount.
104 The TRUE option has been used at the end of the function to indicate that the values
105 down the side of the Discount Table are sorted.
106 Using TRUE will allow the function to make an approximate match. If the Quantity Ordered does
107 not match a value at the side of the Discount Table, the next lowest value is used.
108 Trying to match an order of 125 will drop down to 100, and the discount from
109 the 100 row is used.
110 =VLOOKUP(D126,F114:I116,MATCH(C126,G113:I113,0)+1,TRUE)
111
112 Discount Table
113 Unit Cost Table Brick Wood Glass
114 Brick 2 1 0% 0% 0%
115 Wood 1 100 6% 3% 12%
116 Glass 3 300 8% 5% 15%
117
118
119 Orders Table
120 Item Units Unit Cost Discount Total
121 Brick 100 2 0 188
122 Wood 200 1 0 194
123 Glass 150 3 0 396
124 Brick 225 2 0 423
125 Wood 50 1 - 50
126 Glass 500 3 0 1,275
127
128 Formula for :
129 Uni =VLOOKUP(C126,C114:D116,2,FALSE)
130 Dis =VLOOKUP(D126,F114:I116,MATCH(C126,G113:I113,0)+1,TRUE)
131 Tot =(D126*E126)-(D126*E126*F126)
HLOOKUP

Jan Feb Mar row 1 The row numbers are not needed.
10 80 97 row 2 they are part of the illustration.
20 90 69 row 3
30 100 45 row 4
40 110 51 row 5
50 120 77 row 6

Type a month to look for : Jan

Which row needs to be picked out : 2

The result is : 10 =HLOOKUP(F10,D3:F10,F11,FALSE)

What Does It Do ?
This function scans across the column headings at the top of a table to find a specified item.
When the item is found, it then scans down the column to pick a cell entry.

Syntax
=HLOOKUP(Item To Find, Range To Look In, Row To Pick From, Sorted Or Unsorted)
The Item To Find is a single item specified by the user.
The Range To LookIn is the range of data with the column headings at the top.
The Row To Pick From is how far down the column the function should look to pick from.
The Sorted/Unsorted is whether the column headings are sorted. TRUE for yes, FALSE for no.

Formatting
No special formatting is needed.

Example 1
This table is used to find a value based on a specified month and name.
The =HLOOKUP() is used to scan across to find the month.
The problem arises when we need to scan down to find the row adjacent to the name.
To solve the problem the =MATCH() function is used.

The =MATCH() looks through the list of names to find the name we require. It then calculates
the position of the name in the list. Unfortunately, because the list of names is not as deep
as the lookup range, the =MATCH() number is 1 less than we require, so and extra 1 is
added to compensate.

The =HLOOKUP() now uses this =MATCH() number to look down the month column and
picks out the correct cell entry.

The =HLOOKUP() uses FALSE at the end of the function to indicate to Excel that the
column headings are not sorted, even though to us the order of Jan,Feb,Mar is correct.
If they were sorted alphabetically they would have read as Feb,Jan,Mar.
 Jan Feb Mar
Bob 10 80 97
Eric 20 90 69
Alan 30 100 45
Carol 40 110 51
David 50 120 77

Type a month to look for : feb

Type a name to look for : alan

The result is : 100

=HLOOKUP(E55,C48:E53,MATCH(E56,B49:B53,0)+1,FALSE)

Example 2
This example shows how the =HLOOKUP() is used to pick the cost of a spare part for
different makes of cars.
The =HLOOKUP() scans the column headings for the make of car specified in column A.
When the make is found, the =HLOOKUP() then looks down the column to the row specified
by the =MATCH() function, which scans the list of spares for the item specified in column B.

The function uses the absolute ranges indicated by the dollar symbol $. This ensures that
when the formula is copied to more cells, the ranges for =HLOOKUP() and =MATCH() do
not change.

Maker Spare Cost

Vauxhall Ignition 50 Vauxhall Ford VW
VW GearBox 600 GearBox 500 450 600
Ford Engine 1200 Engine 1000 1200 800
VW Steering 275 Steering 250 350 275
Ford Ignition 70 Ignition 50 70 45
Ford CYHead 290 CYHead 300 290 310
Vauxhall GearBox 500
Ford Engine 1200
1200
=HLOOKUP(A80,F73:H78,MATCH(B80,E74:E78,0)+1,FALSE)

Example 3
In the following example a builders merchant is offering discount on large orders.
The Unit Cost Table holds the cost of 1 unit of Brick, Wood and Glass.
The Discount Table holds the various discounts for different quantities of each product.
The Orders Table is used to enter the orders and calculate the Total.

All the calculations take place in the Orders Table.

The name of the Item is typed in column C.

The Unit Cost of the item is then looked up in the Unit Cost Table.
The FALSE option has been used at the end of the function to indicate that the product
 names across the top of the Unit Cost Table are not sorted.
Using the FALSE option forces the function to search for an exact match. If a match is
not found, the function will produce an error.
=HLOOKUP(C127,E111:G112,2,FALSE)

The discount is then looked up in the Discount Table

If the Quantity Ordered matches a value at the top of the Discount Table the =HLOOKUP will
look down the column to find the correct discount.
The TRUE option has been used at the end of the function to indicate that the values
across the top of the Discount Table are sorted.
Using TRUE will allow the function to make an approximate match. If the Quantity Ordered does
not match a value at the top of the Discount Table, the next lowest value is used.
Trying to match an order of 125 will drop down to 100, and the discount from
the 100 column is used.
0

Unit Cost Table

Brick Wood Glass
2 1 3

Discount Table
1 100 300
Brick 0% 6% 8%
Wood 0% 3% 5%
Glass 0% 12% 15%

Orders Table
Item Units Unit Cost Discount Total
Brick 100 2 0.06 188
Wood 200 1 0.03 194
Glass 150 3 0.12 396
Brick 225 2 0.06 423
Wood 50 1 0 50
Glass 500 3 0.15 1275

Unit Cost 1 =HLOOKUP(B127,D112:F113,2,FALSE)

Discount 0.15 =HLOOKUP(C128,D116:F119,MATCH(B128,C117:C119,0)+1,TRUE)

Exercise

Name Walter Jesse Saul Hank Gustavo

Chemicals 80,000 100,000 72,000 64,000 96,000
Equipment 50,000 62,500 45,000 40,000 60,000
Toys 15,000 18,750 13,500 12,000 18,000
Powder 70,000 87,500 63,000 56,000 84,000
Cars 20,000 25,000 18,000 16,000 24,000
Apparel 8,000 10,000 7,200 6,400 9,600
Food 2,000 2,500 1,800 1,600 2,400

HLOOKUP
Lookup powder expenses for below people
Hank 56,000
Gustavo
Excel Function Dictionary Index_Match
© 1998 - 2000 Peter Noneley Page 36 of 44

A B C D E F G H I J K L M N O P Q
1 Index_Match
2
3 Names Values
4 Bob 250 Exercise
5 Alan 600 Use Index Match Function to find Net Income for Year 2018
6 David 1000
7 Carol 4000 2016 2017 2018 2019
8 Revenue 10,000 11,000 12,100 13,310
9 Type a name to look for : Alan Type a value : 1000 Gross Profit 6,000 6,600 7,260 7,986
10 EBITDA 5,000 5,500 6,050 6,655
11 The position of Alan is : 2 Value position : 3 EBIT 4,000 4,400 4,840 5,324
12 =MATCH(E9,E4:E7,0) =MATCH(I9,I4:I7,1) Net Income 3,000 3,300 3,630 3,993
13 EPS 30.00 33.00 36.30 39.93
14 What Does It Do ?
15 This function looks for an item in a list and shows its position.
16 It can be used with text and numbers. Year 2018
17 It can look for an exact match or an approximate match. Line item Net Income
18 Result
19 Syntax
20 =MATCH(WhatToLookFor,WhereToLook,TypeOfMatch)
21 The TypeOfMatch either 0, 1 or -1.
22
23 Using 0 will look for an exact match. If no match is found the #NA error will be shown.
24
25 Using 1 will look for an exact match, or the next lowest number if no exact match exists.
26 If there is no match or next lowest number the error #NA is shown.
27 The list of values being examined must be sorted for this to work correctly.
28
29 Using -1 will look for an exact match, or the next highest number if no exact match exists.
30 If there is no exact match or next highest number the error #NA is shown.
31 The list must be sorted for this to work properly.
32
33 Examples 1
34 Using the 0 option suitable for an exact match.
35 The Ascending list gives the exact match.
36 The Descending list gives the exact match.
37 The Wrong Value list cannot find an exact match, so the #NA is shown.
38
39 Ascending Descending Wrong Value
40 10 40 10
41 20 30 20
42 30 20 30
43 40 10 40
44
45 20 20 25
46 2 3 #N/A
47 =MATCH(G45,G40:G43,0)
48
49
50 Example 2
51 Using the 1 option suitable for a ascending list to find an exact or next lowest match.
52 The Ascending list gives the exact match.
53 The Descending list gives the #NA error.
54 The Wrong Value list finds the next lowest number..
55
56 Ascending Descending Wrong Value
57 10 40 10
58 20 30 20
59 30 20 30
60 40 10 40
61
62 20 20 25
63 2 #N/A 2
64 =MATCH(G62,G57:G60,1)
65
66
67 Example 3
68 Using the -1 option suitable for a descending list to find an exact or next highest match.
69 The Ascending list gives the #NA error.
70 The Descending list gives the exact match.
71 The Wrong Value list finds the next highest number.
72
73 Ascending Descending Wrong Value
74 10 40 40
75 20 30 30
76 30 20 20
77 40 10 10
78
79 20 20 25
80 #N/A 3 2
81 =MATCH(G79,G74:G77,-1)
82
83
84 Example 4
85 The tables below were used to by a bus company taking booking for bus tours.
86 They need to allocate a bus with enough seats for the all the passengers.
87 The list of bus sizes has been entered in a list.
88 The number of passengers on the tour is then entered.
89 The =MATCH() function looks down the list to find the bus with enough seats.
90 If the number of passengers is not an exact match, the next biggest bus will be picked.
91 After the =MATCH() function has found the bus, the =INDEX() function has been used
92 to look down the list again and pick out the actual bus size required.
Excel Function Dictionary Index_Match
© 1998 - 2000 Peter Noneley Page 37 of 44

A B C D E F G H I J K L M N O P Q
93
94 Bus Size Passengers on the tour : 23
95 Bus 1 54 Bus size needed : 50
96 Bus 2 50 =INDEX(D95:D99,MATCH(H94,D95:D99,-1),0)
97 Bus 3 22
98 Bus 4 15
99 Bus 5 6
100
101
102 Example 5
103 The tables below were used by a school to calculate the exam grades for pupils.
104 The list of grade breakpoints was entered in a list.
105 The pupils scores were entered in another list.
106 The pupils scores are compared against the breakpoints.
107 If an exact match is not found, the next lowest breakpoint is used.
108 The =INDEX() function then looks down the Grade list to find the grade.
109
110 Exam Score Grade Pupil Score Grade
111 - Fail Alan 60 Pass
112 50 Pass Bob 6 Fail
113 90 Merit Carol 97 Distinction
114 95 Distinction David 89 Pass
115 =INDEX(D111:D114,MATCH(G114,C111:C114,1),0)
RATE

What Does It Do ?
Returns the interest rate per period of an annuity
annuity = a fixed sum of money paid to someone each year
Syntax
RATE(Period, paymet, Present value, [Future value], [type], [guess])

Nper Required. The total number of payment periods in an annuity.

Pmt Required. The payment made each period and cannot change over the life of the annuity. Typically, pmt includes prin
Pv Required. The present value — the total amount that a series of future payments is worth now.
Fv Optional. The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assum
Type Optional. The number 0 or 1 and indicates when payments are due

Exercise
What is the yield or return on this fixed deposit? Price to earn FV of 10,000 with below terms?
Terms Terms
N 12 FD matures in 10 years N 15
PV (10,000) Current price is 9,500 Yield 10.0%
PMT Annual payment of 0 PMT 500
FV 20,000 Principal repayment of 10,000 FV 10,000
MODE END Payment at the end of period MODE END

Yield PV

Compound annual growth rate (CAGR)

The compound annual growth rate (CAGR) is a useful measure of growth over multiple time periods.

What is the CAGR for historical and forward period?

2011 2012 2013 2014 2015 E 2016 E 2017 E
Revenues 10,000 10,800 11,772 13,185 14,899 17,133 20,046
Annual Growth 8.0% 9.0% 12.0% 13.0% 15.0% 17.0%
e annuity. Typically, pmt includes principal and interest but no other fees or taxes. If pmt is omitted, you must include the fv argument.
is worth now.
ment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0). If fv is omitted, you must include the pmt argumen

FV of 10,000 with below terms? What will be the maturity value? To earn 8% return what should
Terms Terms
FD matures in 15 years N 20 Bond matures in 20 years N
Annual return of 10% PV (10,000) Current price is 10,000 PV
Annual payment of 500 PMT 700 Annual payment of 700 FV
Principal repayment of 10,000 Yield 8.0% Annual return of 8% Yield
Payment at the end of period MODE END Payment at the end of period MODE

FV PMT

le time periods.

23

CAGR
2011 - 2014 2015 - 2017
9.7%
lude the fv argument.

ou must include the pmt argument.

To earn 8% return what should be the annual payment?

Terms
20 Bond matures in 20 years
(10,000) Current price is 10,000
20,000 Maturity Value of 20,000
8.0% Annual return of 8%
END Payment at the end of period
IRR/XIRR

What Does It Do ?
Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. To calculate the internal rate of return f

Syntax
=XIRR(values, dates, [guess])
Values: Required. A series of cash flows that corresponds to a schedule of payments in dates. The first payment is optional and correspo
Dates: Required. A schedule of payment dates that corresponds to the cash flow payments. Dates may occur in any order. Dates should
Guess: Optional. A number that you guess is close to the result of XIRR.

Formatting
No special formatting is needed.
Exercise
What is the IRR and XIRR for these investments
Project A Project B
Year Cashflows Cashflows Comments
0 (15,000) (25,000) Initial Investment
1 - 10,000 Income
2 5,000 7,000 Income
3 6,000 6,000 Income
4 7,000 5,000 Income

IRR after 2 years 6.07%

IRR after 3 years
IRR after 4 years

Project A Project B
Date Cashflows Cashflows Comments
30-04-2014 (15,000) (25,000) Initial Investment
30-10-2014 - 10,000 Income
30-05-2015 5,000 7,000 Income
30-01-2016 6,000 6,000 Income
30-10-2016 7,000 5,000 Income

IRR as of May-2015
IRR as of Jan-2016
IRR as of Oct-2016
culate the internal rate of return for a series of periodic cash flows, use the IRR function.

t payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment. If the first value is a cost or payment, it m
y occur in any order. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008

Exercise
Calculate retuern earned on SIP

11/30/2016 (5,000.0)
12/31/2016 (5,000.0)
1/31/2017 (5,000.0)
2/28/2017 (5,000.0)
3/31/2017 (5,000.0)
4/30/2017 (5,000.0)
5/31/2017 (5,000.0)
6/30/2017 (5,000.0)
7/31/2017 (5,000.0)
8/31/2017 (5,000.0)
7/18/2024 65,000.0
e first value is a cost or payment, it must be a negative value. All succeeding payments are discounted based on a 365-day year. The series of values m
ctions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.
ay year. The series of values must contain at least one positive and one negative value.