turning numbers into knowledge nate moore mba, cpa, cmpe

17
Turning Numbers Into Knowledge Nate Moore MBA, CPA, CMPE

Upload: shona-joseph

Post on 23-Dec-2015

215 views

Category:

Documents


0 download

TRANSCRIPT

Page 1: Turning Numbers Into Knowledge Nate Moore MBA, CPA, CMPE

Turning Numbers

Into Knowledge

Nate MooreMBA, CPA, CMPE

Page 2: Turning Numbers Into Knowledge Nate Moore MBA, CPA, CMPE

Verifiably Vital Virtues Vested in Versatile

Microsoft Excel® VLOOKUP

Page 3: Turning Numbers Into Knowledge Nate Moore MBA, CPA, CMPE

Learning Objectives

• Work through a basic VLOOKUP

application

• Combine functions to add power to

VLOOKUP

• Watch for and overcome VLOOKUP

gotchas

Page 4: Turning Numbers Into Knowledge Nate Moore MBA, CPA, CMPE

Work Through a BasicVLOOKUP Application

• VLOOKUP(lookup_value, table_array,

col_index_num, [range_lookup])

Page 5: Turning Numbers Into Knowledge Nate Moore MBA, CPA, CMPE

Lookup Value

What am I looking for?

Page 6: Turning Numbers Into Knowledge Nate Moore MBA, CPA, CMPE

Table Array

What table has the data I’m looking for

(in the far left column)

Page 7: Turning Numbers Into Knowledge Nate Moore MBA, CPA, CMPE

Column Index Number

Which column in the table

do I want to return?

Page 8: Turning Numbers Into Knowledge Nate Moore MBA, CPA, CMPE

Range Lookup

TRUE – Find the closest value to what

I’m searching for, without going over

FALSE – Find an exact match

Page 9: Turning Numbers Into Knowledge Nate Moore MBA, CPA, CMPE

Range Lookup – Continued

Range Lookup is Optional and

TRUE is the default!

Table has to be sorted for TRUE to

work!

Page 10: Turning Numbers Into Knowledge Nate Moore MBA, CPA, CMPE

Combine Functions to Add Power to VLOOKUP

• IFERROR (trap and control errors)

• LEFT (lookup leftmost x characters)

• ROW & COLUMN for column number

• HLOOKUP

Page 11: Turning Numbers Into Knowledge Nate Moore MBA, CPA, CMPE

Watch For and Overcome VLOOKUP Gotchas

• Text vs. Numbers (especially CPT

codes)

• Convert Text to Numbers and vice

versa using Text to Columns on the

Data tab

Page 12: Turning Numbers Into Knowledge Nate Moore MBA, CPA, CMPE

Watch For and Overcome VLOOKUP Gotchas

• Absolute references for the table

($A$2:$C$198)

• TRUE is the default!

Page 13: Turning Numbers Into Knowledge Nate Moore MBA, CPA, CMPE

Applications for VLOOKUP in a Medical Practice

• Fee Schedule Analysis – CPT Codes

• Diagnosis Codes

• Patient Satisfaction Surveys

• Financial Reporting

Page 14: Turning Numbers Into Knowledge Nate Moore MBA, CPA, CMPE

Next Steps

Using VLOOKUP to Combine Data in

Microsoft Excel article at

mooresolutionsinc.com/articles.php

MGMA Excel Users Community

Page 15: Turning Numbers Into Knowledge Nate Moore MBA, CPA, CMPE

More Next Steps

Watch Excel Videos 61-69 and 173-176

at mooresolutionsinc.com

Practice with this sample data

Page 16: Turning Numbers Into Knowledge Nate Moore MBA, CPA, CMPE

How to Join Excel Users

• Login to www.mgma.com• Go to My Profile, then click on My

Subscriptions from the submenu• Choose your delivery preferences for the

communities you wish to join• Direct link

http://community.mgma.com/MGMA/MGMA/MyProfile/MySubscriptions/Default.aspx

• Excel Users is in alphabetical order

Page 17: Turning Numbers Into Knowledge Nate Moore MBA, CPA, CMPE

MooreSolutionsInc.com

Nate Moore