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

1. Day 6:MICROSOFT EXCEL – CHAPTER 2 Contd.MICROSOFT EXCEL – CHAPTER 3 AkhilaKondai akhila.kondai@mail.wvu.edu September 04, 2013

2. Microsoft Excel – Chapter 2 CONTD.

3. 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.

4. VLOOKUP • Used to gauge an entered value against a range of corresponding results • Well suited for large tables of data, such as tax tables • Also to convert the number grades into letter grades • Has three mandatory arguments

5. 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

6. Example Converting the Number grades to the letter grades

7. 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

8. Financial Functions • Used for decisions involving payments, investments, interest rates, etc. • Allows you to consider several alternatives • PMT

9. 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

10. 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 )

11. 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.

12. 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)

13. 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.

14. 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)

15. 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

16. Microsoft Excel – Chapter 3

17. 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.

18. 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.

19. 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.

20. 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)

21. 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

22. Imports • Import data from other sources

23. 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

24. 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

25. Create a Web Query

26. 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

27. Refresh Connections • Display Connections • Set Connection Properties

28. Manage Connections

29. 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

30. Convert Text to Columns

31. 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)

32. Manipulate Text with Functions

33. Text Manipulation • Other text functions

34. 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.

35. 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.

36. Questions ?