1 / 36

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

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

patch
Télécharger la présentation

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

An Image/Link below is provided (as is) to download presentation 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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 ?

More Related