Day 6: MICROSOFT EXCEL – CHAPTER 2 Contd. MICROSOFT EXCEL – CHAPTER 3

1 / 36

# Day 6: MICROSOFT EXCEL – CHAPTER 2 Contd. MICROSOFT EXCEL – CHAPTER 3 - PowerPoint PPT Presentation

Day 6: MICROSOFT EXCEL – CHAPTER 2 Contd. MICROSOFT EXCEL – CHAPTER 3. Akhila Kondai akhila.kondai@mail.wvu.edu September 04, 2013. Microsoft Excel – Chapter 2 CONTD. Lookup Functions. Lookup functions are used to look up values in a table to perform calculations or display results

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about 'Day 6: MICROSOFT EXCEL – CHAPTER 2 Contd. MICROSOFT EXCEL – CHAPTER 3' - patch

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

### Day 6:MICROSOFT EXCEL – CHAPTER 2 Contd.MICROSOFT EXCEL – CHAPTER 3

AkhilaKondai

akhila.kondai@mail.wvu.edu

September 04, 2013

Lookup Functions
• Lookup functions are used to look up values in a table to perform calculations or display results
• A Lookup table is a range that contains data for the basis of the lookup and data to be retrieved.
VLOOKUP
• Used to gauge an entered value against a range of corresponding results
• Well suited for large tables of data, such as tax tables
• Has three mandatory arguments
VLOOKUP(contd.)
• Syntax

=VLOOKUP(lookup value , lookup table , column index number , [range lookup])

• The lookup value ─ value to look up in a reference table
• The lookup table ─ a range of cells containing the reference table
• The column index number ─ the column number in the lookup table that contains return values
Example

Converting the

HLOOKUP Function
• The HLOOKUP function is used when the breakpoints and return data are placed in rows
• The third argument now lists the row index
Financial Functions
• Used for decisions involving payments, investments, interest rates, etc.
• Allows you to consider several alternatives
• PMT
PMT Function
• Used to calculate loan payments
• Has three arguments:
• the interest rate per period
• the number of periods
• the amount of the loan
• Computes the associated payment on a loan
PMT(contd.)
• Syntax:

Pmt( interest_rate, number_payments, PV, [FV],[Type] )

• Example:
• Pmt(7%/12, 2*12, 5000, 0, 0)
• returns the monthly payment on a \$5,000 loan

at an annual rate of 7%. The loan is paid off in

2 years (ie: 2 x 12).

• E2 - 7% ; E3 - 24 ; E4 - \$5,000.00
• PMT ( E2/12 , E3 , - E4 )
Range Names
• A range name is a word or phrase used to identify a cell or cell range
• Range names make formulas easier to read
• Range names use the following rules:
• 1to 255 characters
• Begin with a letter or underscore (_)
• Contain letters, digits, period, underscore.
Creating and Maintaining a Range Name
• Type the range name in the Name Box area
• Enter the name using New Name dialog box
• Name Manager dialog box is used to edit or delete a range name
• Insert a table of Range Names ( Use in Formula -> Paste Names)
Rank Function
• The RANK function is useful for producing a ranking by using a formula
• Syntax

=RANK(number, range, order)

number: the number/cell for which you want to find the ranking within the “range”

range: range of numbers/cells within which the “number” will be ranked

order: not really required for our purposes. Leave it blank do not put anything.

Rank Function Example
• Insert a Rank function in cell D3
• Type =rank(c3,c3:c8)
• Autofill the formula to remaining cells. Observe the cell references and make the references absolute
• =rank(c3,\$c\$3:\$c\$8)
Conditional Formatting
• Formats the data in excel sheet if and only if the data satisfies the condition.
• We wish to apply conditional formatting for the values in column3
• Select all the values in column3

->Click on conditional formatting in styles group

->Select the type of condition which you want to give

->Give the condition

Importing
• Importing –Inserting data from one application or file into another.
• Text, CSV, XML and Database files are the commonly imported files.
• Before importing we have to check if we want to manage the data as a separate entity in Excel or you want a connection to the original data source/application.
Text files
• The text file has .txt extension and contains only text (alphabets, numbers and symbols).
• We must choose a delimiter for importing.

Ex: Space, tab or Comma etc

• Columns align according to the delimiters.
CSV Files
• CSV abbreviated as Comma-Separated Values
• Text file with comma as delimiter.
• Excel imports the text between the commas in the text file into individual cells.
Importing a Text File
• ->Data Ribbon

->Click “from text” which is in External data group

->Browse the location of text file

->Open the text file

• Text import wizard (3 steps)
• Select the type that describes the data
• Select the type of delimiter in your data
• Select the format of column data
• Select the location (where to import the data in your sheet)
Importing an Access Database Table or Query
• ->Data ribbon

->Click “from access” in External data group

->Browse the location of file

->Open the file

• Import Wizard
• Select the table in database which you want to import into your sheet
• Select the location where you want to place the imported data
Imports
• Import data from other sources
Create a Web Query
• A Web query enables Excel to go to a particular Web site to obtain information
• Web queries let you extract data from tables on a Web page
• We need this to display live stock prices, traffic reports, airport delays etc.
• If the Web address changes, you must change the Web query and URL to ensure you have the most accurate information and citation
• If you have to log in to the Web site, the query generally will not work
create a web query
• Select “FROM WEB” in Get External Data column.
• Specify the ULR in ADDRESS ( u can see the page by clicking GO )
• Select IMPORT
• Select cells where to put data
Manage Connections
• When you import data using the options in the Get External Data group, Excel creates a link to the original data source
• Refresh connections periodically to ensure you have the most up-to-date and accurate information
• View or remove existing connections through the Workbook Connections dialog box

Refresh Connections

• Display Connections
• Set Connection Properties
Convert Text to Columns
• In the imported data, the data may not be structured in a way that meets your needs.
• If data is displayed in one column, use the Text to Columns command to separate the data into multiple columns
• Convert Text to Columns Wizard guides you through the process of separating data and choosing a delimiting character
Text Manipulation
• Converting text to columns.
• Select the cell or range which contains the delimited text
• Go to Data ribbon->Data tools->Text to columns
• Manipulating Text with functions.
• Concatenate Function combines the text =CONCATENATE(text1,text2)
• Proper Function capitalizes the first letter in a text string =PROPER(text)
• Upper Function converts the text to upper case =UPPER(text)
• Lower Function converts all upper case letters to lower case =LOWER(text)
Text Manipulation
• Other text functions
XML Files
• XML is eXtensible Markup Language
• Files can contain a significant amount of machine readable data and allow us to easily import it into Microsoft Office to work with
• Steps to import
• Go to Data tab->From Other Sources->From XML Data Import
• Choose the XML file and click OK.
• Choose the cell from where you want the data to be inserted from the XML file.
Reminder
• Homework 1 is now posted. It is due on 09/13/13 by 11:59 P.M.
• A workshop is being conducted on ‘Installing a windows virtual machine on Mac’, this Friday (6th Sept 2013) at 1.00 P.M in 206 Armstrong hall.