1 / 26

CHAPTER

3. CHAPTER. BASIC APPLICATION SOFTWARE. Announcement: QUIZ#02. In Lecture Session # 9 (5 th Week- 1 st Lecture) Contents : Lecture slides (Lec#5 – Lec#8) Text book (Computing Essentials 2004) Chapter 2 (spreadsheet details) Note : Students are advised to bring PENCILS essentially.

samanthao
Download Presentation

CHAPTER

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. 3 CHAPTER BASIC APPLICATION SOFTWARE

  2. Announcement: QUIZ#02 • In Lecture Session # 9 (5th Week- 1st Lecture) • Contents: • Lecture slides (Lec#5 – Lec#8) • Text book (Computing Essentials 2004) • Chapter 2 (spreadsheet details) • Note: • Students are advised to bring PENCILS essentially

  3. Lecture Objectives • Explaining Spreadsheet Functions • Use of Function Wizard • Use of Cell/ Range Naming in Spreadsheet Functions • Function Examples in Microsoft Excel • Financial Functions • Logical Functions • Math/ Statistical Function • Lookup & Reference Functions • Data/ Time Functions

  4. Spreadsheet Functions

  5. Spreadsheet Functions • A function is a calculation shortcut to some (possibly, complex) formula. • Every function is based upon some predefined formula, working behind the scene. • Functions allow the users to avoid the mathematical details of complex calculations. • For Example: • Function =SUM(A1,A2,A3) is based upon the sum formula =A1+A2+A3.

  6. Spreadsheet Functions Components • Function Syntax • Function name (spelling), • Right order of different function parts (argument sequence.) • Proper use of comma, parentheses and other punctuations. • Function Arguments (parameters) • The values/ cell references, the function needs to work upon for result calculation. • Number of arguments could be fixed or unlimited depending upon the nature of the function used. • Function Result • Every function results only in one value.

  7. Spreadsheet Functions Components General Function Syntax Function Name( argument1, argument2, ………) • Function Examples • Function with variable number of arguments SUM(A1, A2, A3) - adding 3 values SUM(A1, A2, A3,A4,A5) – adding 5 values • Function with fixed number of arguments SQRT(A1) – finding the square root for A1 ROUND(A1,2) – return the decimal value in A1 with 2 digits after decimal point.

  8. Use of Functions in Spreadsheet Programs • Microsoft Excel • Similar in use as a formula (must start with =). • Can be extended to a formula by using operators & other operands. =SUM(A1,A2,A3)/3.0 -calculating average of 3 values. =AVERAGE(A1:A3) – calculating average of A1, A2 & A3. • Lotus 1-2-3 • Must start with @. @SUBTOTAL(C3..C5) -calculating total of C3, C4 & C5.

  9. Use of Cell Ranges in Function Arguments • Cell ranges is a shortcut for specifying multiple contiguous cells by one concise expression. • General Syntax: [Beginning Cell] [Separator] [Ending Cell] Where • Beginning Cell reference can not have a higher value of row or column when compared with the Ending Cell. • Separator character may be different in different spreadsheet packages. • Microsoft ExcelSeparator = : • Lotus 1-2-3 Separator = ..

  10. ….Use of Cell Ranges • Examples (Valid & Invalid Cell Range Expressions in Microsoft Excel) • C1:C5 Valid, all cells in the column C between row 1 & 5 (5 cells). • C5: C1Invalid, first cell (C5) is greater than the last one (C1) in rows. • A1: C5Valid, include all cells in the square region A1 till C5.

  11. ….Use of Cell Ranges • Examples (Microsoft Excel) • Sheet1:Sheet3!A1 • Valid, includes cell A1 from Sheet1, Sheet2 & Sheet3. • Sheet1!A1 : Sheet3!A1 • Invalid syntax. • Sheet1:Sheet3!A1:A3 • Valid, includes cells A1:A3 from all 3 sheets (9 cells) Using 3D Sheet Referencein Microsoft Excel [Beginning Sheet] : [Ending Sheet] ! [Cell Range]

  12. Using Function in Microsoft Excel • Microsoft Excel provides an easy to use interface for using functions. • Paste Function Window • Categorizes the big function list into smaller sub-lists. • Alphabetically ordered. • Provides help while selecting arguments.

  13. Naming Cells & Cell Ranges • While writing formulas, meaningful cell names are more desirable than their coded references. =unit_price * no_of_units - easier to understand =A3 * B5 – difficult to interpret. • How to define names? (Microsoft Excel) • Position the cursor on the cell OR select the cell range. • Go to name box. • Write the meaningful name to be used. • Names could be given to • Single cells • Contiguous cells • Non- contiguous cells

  14. ..Naming Cells & Cell Ranges • Examples of Cell Naming in Microsoft Excel • Single Cell • Contiguous Range C4:C7, Name= Quiz1Scores

  15. Automatic Recalculation =RAND( ) 0.102938421 0.345809321 =ROUND( 12.4352, 3) 12.435 =SIN( PI( )/3 ) 0.8660254037 Function Examples in Microsoft Excel • Microsoft Excel provides a long list of functions in different application areas like finance, statistics, trigonometry etc. • Math & Trigonometry – Functions implementing useful mathematical & Trigonometry formulas. • RAND( )● Returns a random value between 0 and 1. • ROUND( X, Y)● Returns the value X with Y digits after decimal point. • SIN( X )● Returns the Sin for X. • Examples:

  16. =COUNT( A1:A5) 4 =AVERAGE(A1:A5) 5.5 =STDEV(A1:A5) Function Examples in Microsoft Excel • Statistical - Functions used to give summary information about a collection of values. • COUNT( X:Y ) ● Returns the count of numerical values in the X:Y. • AVERAGE( X:Y) ● Returns the mean value in the list X:Y. • STDEV( X:Y) ● Returns the standard deviation of the list X:Y • Examples: Skip the non-numeric values in the list.

  17. Function Examples in Microsoft Excel • Financial – Functions for financial calculations. • PMT(rate, nper, pv, fv, type) • Calculates the payment for a loan based on constant payments and a constant interest rate. • FV(rate, nper, pmt, pv, type) • Returns the future value of an investment based on periodic, constant payment and a constant interest rate.

  18. Function Examples in Microsoft Excel • Logical Functions - • IF( logical_test, value_if_true, value_if_false) • Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE. • AND( logical1, logical2, ….) • Checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE. • OR( logical1, logical2, ….) • Checks whether any of the arguments are TRUE, and returns TRUE or FALSE. Returns FALSE only if all arguments are FALSE.

  19. Retired Back to work Distinction Average Average Average Function Examples in Microsoft Excel • Logical Function Examples: • IF Function • AND Function =IF(Age>60, “Retired”, “Back to work”) =IF( AND(Math>75, English>75) “Distinction”, “Average”)

  20. Retired Close Still Young Function Examples in Microsoft Excel • Logical Function Examples: • Nested IF Functions ● for more than two alternatives. =IF(Age>60, “Retired”,IF (Age>50, “Close”, ”Still Young”))

  21. Use of IF with other functions • COUNTIF • Count only those cells which fulfill some criterion • SUMIF • Sum only those cells which fulfill some criterion

  22. Function Examples in Microsoft Excel • Lookup & Reference Functions: • Functions in this category implements the process of selecting a value from a list of values on the basis of some condition • A large number of conditions may need to be checked (may require a very complex nested IF ) • Example: • A list of grade letters are available to be assigned in a course • The selection of a grade letter will be based upon a test on the final score value. • The score may need to be tested for a number of different score values corresponding to different grade letters. • The functions useful in the scenario will be • LOOKUP, VLOOKUP, HLOOKUP

  23. =CONCATANATE( A2, A3) MIS105 =LEFT( A4, 3) KFU Function Examples in Microsoft Excel • Text Functions - Used to manipulate text values. • CONCATENATE( text1, text2,…) ● Returns a combination of all text values. • LEFT(text1, n) ● Returns first n characters starting from left most character. • Examples:

  24. =TODAY() 3/4/2002 =YEAR ( TODAY() ) 2002 Function Examples in Microsoft Excel • Date & Time Functions - Used to manipulate date and time values. • TODAY( ) ● Returns a serial number which represents today’s date. • YEAR( serial_number ) ● Returns the year corresponding to the serial number (date value) • Examples:

  25. Announcement: QUIZ#02 • In Lecture Session # 9 (5th Week- 1st Lecture) • Contents: • Lecture slides (Lec#5 – Lec#8) • Text book (Computing Essentials 2004) • Chapter 2 (spreadsheet details) • Note: • Students are advised to bring PENCILS essentially

More Related