Functions: Statistical, Mathematical, Financial and Database functions

Lesson Plan: Unit - 02 Microsoft Office

Subject: BELE2CFA: Computer Fundamentals And Applications
Topic of Study: Introduction to Functions & its types with suitable example.
Grade/Level: Bachelor of SCIENCE

Objective: To demonstrate and explain various types of function in Excel - 2010.

Time Allotment: 60 Minutes

15.0 Functions

• Functions is an expression form to calculate or evaluate the value of given cell references.
• There are several functions in excel.

• It is also called formulas. These are ready made to use in worksheet(s).
• For Example:
• Financial formulas
  
• Logical formulas
  
• Text formulas
  
• Date & Time formulas
  
• Lookup & Reference formulas
  
• Mathematical & Trigonometric formulas
  
• others formulas
  

15.0.1 Statistical functions / formulas

• Here, you can use all kind of statistical formulas.
• For example:
• Min: MIN(number1, [number2], ...)
• It is used to find a minimum value.
• Max: MAX(number1, [number2], ...)
• It is used to find a maximum value.
  
• Large: LARGE(array, k)
• It is used to find largest value. Here, 3rd large value will find using formulas.
  
• Small: SMALL(array, k)
• It is used to find smallest value. Here, 2nd small value will find using formulas.
  
• Median: MEDIAN(number1, [number2], ...)
• It is used to find median value. (middle number)
  
• Mode: MODE(number1,[number2],...])
• It is used to find mode value. (most frequently occurring number)
  
• Standard Deviation: STDEV(number1,[number2],...])
• It is used to find SD value.
  
• Average: AVERAGE(number1, [number2], ...)
• It is used to find average value.
  

15.0.2 Mathematical

• Here, you can use all kind of mathematical formulas.
• For example:
• Abs: ABS(number)
• It is used to returns the absolute value of a number.
  
• Ceiling: CEILING(number, significance)
• It is used to round value in the round up value.
  
• Floor: FLOOR(number, significance)
• It is used to round value in the round down value.
  
• Mround: MROUND(number, multiple)
• It is used to round value with multiplication form. Here, multiplication of 10 value.
  
• Int: INT(number)
• It is used to convert given number into integer value.
  
  
  
  
• Trunc: TRUNC(number, [num_digits])
• It is used to convert chop off value as per given decimal point.
  
• Even: EVEN(number)
• It is used to convert a positive/negative number into nearest even number.
  
• Odd: ODD(number)
• It is used to convert a positive/negative number into nearest odd number.
  

15.0.3 Financial

• Here, you can use all kind of Financial formulas.
• For example:
• PMT: PMT(rate, nper, pv, [fv], [type])
• It is used to calculcate the payment for the loan.
  
  
  
  
• RATE: RATE(nper, pmt, pv, [fv], [type], [guess])
• It is used to return interest rate per period.
  
• NPER: NPER(rate,pmt,pv,[fv],[type])
• It is used to number of period for investment.
  
• PV: PV(rate, nper, pmt, [fv], [type])
• It is used to find present value.
  
• FV: FV(rate,nper,pmt,[pv],[type])
• It is used to find future value. 
  
  
  
  

15.0.4 Database

• Here, you can use all kind of Database formulas.

• For example:
• DSUM: DSUM(database, field, criteria)
• Adds the numbers in a field (column) of records in a list or database that match conditions that you specify.
  

• The above Dsum function calculates the sum of the values in cells D10, D11, D14 & D15, and therefore returns the value $1,210,000. 

• DCOUNT: DCOUNT(database, field, criteria)
• Counts the cells that contain numbers in a database.
  

• The above Dcount function finds that there are 2 rows for which the Gender is "Male" and the Subject is "Science". However, only one of these rows (row 13) contains a number in the "Score" column. Therefore, the function returns the value 1. 

• DMAX: DMAX(database, field, criteria)
• Returns the maximum value from selected database entries.
  

• The above Dmax function calculates the maximum of the values in cells D6 & D7, and therefore returns the value $340,000.


• DMIN: DMIN(database, field, criteria) 
• Returns the minimum value from selected database entries

• The above Dmin function calculates the minimum value in cells E10 & E13, and therefore returns the value 48%.

• DGET: DGET(database, field, criteria)
• Extracts from a database a single record that matches the specified criteria.
  

• The above Dget function finds the record in row 17, and returns the value from the "Sales" column. Therefore, the function returns the value $188,000.