1 / 24

Day 5: MICROSOFT EXCEL – CHAPTER 2 CONTD.

Day 5: MICROSOFT EXCEL – CHAPTER 2 CONTD. Akhila Kondai akhila.kondai@mail.wvu.edu September 02, 2014. Reminder. MyITLab Lesson A is due on 09/08/14, Monday. Page setup and Printing. The Page Setup Dialog Box Launcher contains many common print-related options.

gazit
Download Presentation

Day 5: MICROSOFT EXCEL – CHAPTER 2 CONTD.

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 5:MICROSOFT EXCEL – CHAPTER 2 CONTD. AkhilaKondai akhila.kondai@mail.wvu.edu September 02, 2014

  2. Reminder • MyITLabLesson A is due on 09/08/14, Monday.

  3. Page setup and Printing • The Page Setup Dialog Box Launcher contains many common print-related options

  4. Page setup and layout - example • At some point you have to print your worksheets • Click on Page Layout and the icon for Page Setup • Now you can specify options for whether the page will be in Portrait or Landscape style • Whether you want to fit the contents to a single page • You can also specify margins • You can also set up headers and footers • You can also print gridlines, row and column headings, etc under the Sheet tab

  5. More Math and Statistical Functions

  6. Date/Time Functions

  7. Logical Functions • IF function • Logical Operators

  8. IF Function • Used to determine whether a given condition has been satisfied or not • When the condition is met, the formula performs one task; when it is not met, the formula performs another task • Has three arguments: • a condition tested to determine if it is true or false • the resulting value if the condition is true • the resulting value if the condition is false

  9. IF (contd.) • Syntax: IF(condition, value_if_true, value_if_false) Value when condition is false Value when condition is true Condition is true or false • IF(H4=100, “Good”, “Bad”)

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

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

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

  13. Example Converting the Number grades to the letter grades

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

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

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

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

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

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

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

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

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

  23. ANNOUNCEMENT • A workshop is being conducted on ‘Installing a windows virtual machine on Mac’, this Friday (5th Sept 2013) at 1.00 pm at 206 Armstrong Hall.

  24. Questions ?

More Related