CP Lab – Experiment 5

EXPERIMENT NO 5

Aim:  Working with various excel formulas and functions

Objectives:  Understand and demonstrate various formulas and functions of MS Excel

Hardware requirements: Any CPU with Pentium Processor or similar, 256 MB RAM or more, 1 GB Hard Disk or more.

Software requirements:  Windows 64 bit / Ubuntu 14 Operating System, MS office 2010 or later, Open Office for Ubuntu

Theory: the worksheet is arranged in “columns” (denoted by letters) and “rows” (indicated by numbers). Each location is a “cell” and a group of cells is known as a “range.”

Function: A function is a calculation or operation that returns a result. The inputs in a function are called “arguments.” All functions begin with an equals sign [=]. That way Excel knows not to treat the arguments as text. For example, = AVERAGE(2,4) is a function but AVERAGE(2,4) is just a string of text. Without an equals sign, Excel will not calculate a result. The arguments in this function are 2 and 4.

Note, Excel uses upper-case letters to list functions, but you can use lower or upper-case letters when you write them.

In Excel, the “Function Library” can be found on the “Formulas” tab.

There are 13 categories of functions, some of which are:

  • Mathematical: AVERAGE() – calculates the average of a series of numbers.
  • Date and time: DATEVALUE() – converts a string of text like “30 November 2013” to a number so that you can use this number in other date and time functions. You cannot do math with dates unless you convert them to numbers first.
  • Text: LEN() – returns the length of a string. For example =LEN(“Excel”) is 5.
  • Logical: IF() – the IF() function is written like =IF(<test>, then A, else B). So, if “test” is true, then the result is A; if “test” is not true, then B.
  • Lookup and Reference: These are needed to lookup values elsewhere in the spreadsheet. For example, VLOOKUP looks in a table of values to find one cell.

There are also special functions for financial, engineering, and statistics which are listed separately on the “More Functions” menu.

Formula: A formula is combination of “operators”, “operands”, and “functions.” For example, the function =SUM adds a list of numbers. You use a formula like doing a calculation by hand. For example, you could put your family budget into a formula like this:

Remaining cash = (4 * weekly salary) – mortgage – food – utilities

The operators are multiply [*] and subtract [-]. The operands are the values “weekly salary”, “mortgage”, “food”, and “utilities.” The result is “remaining cash.”

Following are some common and important excel functions.

Excel Text Functions

Functions to Convert Between Upper & Lower Case
LOWER Converts all characters in a supplied text string to lower case
PROPER Converts all characters in a supplied text string to proper case (i.e. letters that do not follow another letter are upper case and all other characters are lower case)
UPPER Converts all characters in a supplied text string to upper case
UNICHAR Returns the Unicode character that is referenced by the given numeric value
UNICODE Returns the number (code point) corresponding to the first character of a supplied text string
Information Functions
LEN Returns the length of a supplied text string
FIND Returns the position of a supplied character or text string from within a supplied text string (case-sensitive)
SEARCH Returns the position of a supplied character or text string from within a supplied text string (non-case-sensitive)

Excel Logical Functions

Boolean Operator Functions
AND Tests a number of user-defined conditions and returns TRUE if ALL of the conditions evaluate to TRUE, or FALSE otherwise
OR Tests a number of user-defined conditions and returns TRUE if ANY of the conditions evaluate to TRUE, or FALSE otherwise
NOT Returns a logical value that is the opposite of a user supplied logical value or expression
Conditional Functions
IF Tests a user-defined condition and returns one result if the condition is TRUE, and another result if the condition is FALSE
SWITCH Compares a number of supplied values to a supplied test expression and returns a result corresponding to the first value that matches the test expression.

Excel Date and Time Functions

Current Date & Time
NOW Returns the current date & time
TODAY Returns today’s date
Extracting The Components of a Time
HOUR Returns the hour part of a user-supplied time
MINUTE Returns the minute part of a user-supplied time
SECOND Returns the seconds part of a user-supplied time

Excel Engineering Functions

Converting Between Bases
BIN2DEC Converts a binary number to a decimal
BIN2HEX Converts a binary number to hexadecimal
BIN2OCT Converts a binary number to octal
DEC2BIN Converts a decimal number to binary

Excel Statistical Functions

Count & Frequency
COUNT Returns the number of numerical values in a supplied set of cells or values
Finding the Largest & Smallest Values
MAX Returns the largest value from a list of supplied numbers
MIN Returns the smallest value from a list of supplied numbers
LARGE Returns the Kth LARGEST value from a list of supplied numbers, for a given value K
SMALL Returns the Kth SMALLEST value from a list of supplied numbers, for a given value K

Conclusion: In this experiment, we have studied use of different functions and formulas.

R.No Name of Student Date of Performance Date of Evaluation Grade Sign of student Sign of Faculty
             

 

Leave a Reply

Your email address will not be published. Required fields are marked *