Vlookup (& Hlookup)
Example Data
Dealer Dealer # Sales
ALERT CELLULAR 0081468 25
ALERT-LOGAN 0081869 45
DIGITAL ETC 0083674 85
Imagine Wireless 0004464 25
WAL-MART - AURORA - EXPOSITION 0083808 55
Wasatch Wireless - Syracuse 0011578 25
WIRELESS ETC. 0084155 47
WIRELESS WHOLESALE 0074393 87
.
…imagine this list to be 1000's of rows long…
.
Example Compilation
Dealer Dealer # Sales
ALERT CELLULAR
Imagine Wireless
WIRELESS ETC.
.
…imagine this list is longer…
.
11/23/2011 Company Confidential b
Example Data
Dealer Dealer # Sales Employees …if…
ALERT CELLULAR 0081468 25 2
ALERT-LOGAN 0081869 45 1
DIGITAL ETC 0083674 85 17
Imagine Wireless 0004464 15 5
WAL-MART - AURORA - EXPOSITION 0083808 55 6
Wasatch Wireless - Syracuse 0011578 25 2
WIRELESS ETC. 0084155 47 4
WIRELESS WHOLESALE 0074393 87 3
Countif
Count the number of dealers that had sales of more than 25:
Sumif
Add up the number of employees working at stores that had more than 25 sales:
11/23/2011 Company Confidential b
Bad
Good
Example
\4492aa79-f770-45f3-a921-2f9f9f8e305d.xlsx Conditional Format 4:00 PM 11/23/2011
Element 1 7
Element 2 5
Element 3 2
Element 4 10
Element 5 6
Example
\4492aa79-f770-45f3-a921-2f9f9f8e305d.xlsx Data Bars (only '07) 4:00 PM 11/23/2011
Number 1 10
Number 2 13
Copy From Copy To
Regular (All) 23
Values 23
Formulas 23
Format 23
Transpose
Vertical List
Item 1
Item 2
Item 3
Item 4
Item 5
Horizontal List
Operation
Item 1 154,325 154,325 154,325
Item 2 155,862 155,862 155,862
Item 3 578,932 578,932 578,932
Item 4 2,487,833 2,487,833 2,487,833
Item 5 224,889 224,889 224,889
Subtract: Divide:
100,000 1,000
Link
Copy This
\4492aa79-f770-45f3-a921-2f9f9f8e305d.xlsx PasteSpecial Options 4:00 PM 11/23/2011
($K) Fav/(Unfav)
Actual Forecast Variance
Commissions 42,815 54,533 (11,718)
Employee Commissions 10,236 10,414 (178)
Indirect Commissions - Postpay 13,000 15,000 (2,000)
Postpay Spiffs 11,778 21,465 (9,688)
Prepay Spiffs 7,012 4,691 2,321
Commission FPD Reserve 159 1,603 (1,445)
Peak Performance 250 940 (690)
Referral Credits 381 420 (39)
\4492aa79-f770-45f3-a921-2f9f9f8e305d.xlsx Copy as Picture 4:00 PM 11/23/2011
List of Choices
Yes
Maybe
No
Yeah Right!
Questions Answers
Do you like cats?
Do you like dogs?
Are you crazy?
11/23/2011 Company Confidential b
Do you
require
Last Name First Name Male / Female lunch? Question # How excited are you for this quarter to be over? Rank Starting Salary
1 JOHNSON LINDSEY ERIN Female Yes Q1 Pretty dang excited 5 100,000
2 JOHNSON KEVIN DION Male Yes Q1 Pretty dang excited 4 90,000
3 OLSEN MATTHEW DAVID Male Yes Q3 I love school more than my life 3 80,000
4 SU XIA Female Yes Q5 Other: Jon, best luck on your company! 2 11,000
5 MOORE MARYANN Female Yes Q5 Other: you have no effing idea. 1 95,000
6 JACOBSON JON FREDERICK Male No Q3 I love school more than my life 5 80,000
7 HUMPHREYS GREGORY KYLE Male No Q3 I love school more than my life 4 95,000
8 KUO YIN-JUNG Female Yes Q1 Pretty dang excited 3 150,000
9 THOMAS SANTOSH Male Yes Q4 What, this quarter is almost over? 2 100,000
10 AU BEATRICE PING-WEN Female Yes Q4 What, this quarter is almost over? 1 90,000
11 GANDHI SHIKHA Female Yes Q1 Pretty dang excited 5 80,000
12 ROWLAND EMILY E Female Yes Q1 Pretty dang excited 4 11,000
13 NISHIDA MASAHIRO Male Yes Q2 Marginally excited 3 95,000
14 PARIKH VANDAN Male Yes Q5 Other: Can't wait to rediscover "free time" 2 80,000
15 SAIMONS BRIANA LAUREN Female Yes Q1 Pretty dang excited 1 11,000
16 KIM JI-YEON YOO Female Yes Q1 Pretty dang excited 5 100,000
17 OAKDALE JONATHAN JAMES Male Yes Q1 Pretty dang excited 4 45,000
11/23/2011 Company Confidential b
Last Name First Name First & Last Question
Male / Female Do you require lunch? # are you for this quarter to be over?
How excitedRank Starting Salary
1 JOHNSON LINDSEY Yes Q1 Pretty dang excited 5 100,000
2 JOHNSON KEVIN Yes Q1 Pretty dang excited 4 90,000
3 OLSEN MATTHEW Yes Q3 3
I love school more than my life 80,000
4 SU Eileen (XIA) Yes Q5 2
Other: Jon, best luck on your company! 11,000
5 MOORE MARYANN Yes Q5 1
Other: you have no effing idea. 95,000
6 JACOBSON JON No Q3 5
I love school more than my life 80,000
7 HUMPHREYS GREGORY No Q3 4
I love school more than my life 95,000
8 KUO YIN-JUNG (Sally) Yes Q1 Pretty dang excited 3 150,000
9 THOMAS SANTOSH Yes Q4 2
What, this quarter is almost over? 100,000
10 AU BEATRICE Yes Q4 1
What, this quarter is almost over? 90,000
11 GANDHI SHIKHA Yes Q1 Pretty dang excited 5 80,000
12 ROWLAND EMILY Yes Q1 Pretty dang excited 4 11,000
13 NISHIDA MASAHIRO Yes Q2 Marginally excited 3 95,000
14 PARIKH VANDAN Yes Q5 2 80,000
Other: Can't wait to rediscover "free time"
15 SAIMONS BRIANA Yes Q1 Pretty dang excited 1 11,000
16 KIM JI-YEON Yes Q1 Pretty dang excited 5 100,000
17 OAKDALE JONATHAN Yes Q1 Pretty dang excited 4 45,000
11/23/2011 Company Confidential b