Excel Functions

Go back to Tutorial

Functions are prewritten formulas that perform certain types of calculations automatically. The syntax or rules of structure for entering all functions is

Function name (arguments1, argument2…)

  • The functions name identifies the type of calculations to be performed.
  • Most functions require that we enter one or more arguments following the function name. An argument is the data the function uses to perform the calculation.
  • The type of data the function requires depends upon the type of calculation being performed.

Using Arguments

Arguments are values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numeric values, text values, cell references, ranges of cells, names, labels, and nested functions.

Using Functions Wizard to create formulae

Following steps are taken to create formulae

  • Click the cell in which we want to enter the formula.
  • To start the formula with the function, in insert menu click function.
  • Type function name or select by selecting “All” in select by category drop down list
  • Click the function we want to add to the formula.
  • Enter the arguments.
  • When we complete the formula, press ENTER.

Using names in formulas

You can use the labels of columns and rows on a worksheet to refer to the cells within those columns and rows. Labels can be used in formulas that refer to data on the same worksheet; if you want to represent a range on another worksheet, use a name.

A defined name in a formula can make it easier to understand the purpose of the formula. For example, the formula =SUM(FirstQuarterSales) might be easier to identify than =SUM(C20:C30).

Names are available to any sheet. For example, if the name ProjectedSales refers to the range A20:A30 on the first worksheet in a workbook, you can use the name ProjectedSales on any other sheet in the same workbook to refer to range A20:A30 on the first worksheet.

Names can also be used to represent formulas or values that do not change (constants).

You can also link to a defined name in another workbook, or define a name that refers to cells in another workbook. For example, the formula =SUM(Sales.xls!ProjectedSales) refers to the named range ProjectedSales in the workbook named Sales By default, names use absolute cell references.

Guidelines for names

  • The first character of a name must be a letter or an underscore character. Remaining characters in the name can be letters, numbers, periods, and underscore characters.
  • Names cannot be the same as a cell reference, such as Z$100 or R1C1.
  • More than one word be used but spaces are not allowed. Underscore characters and periods may be used as word separators— for example, Sales_Tax or First.Quarter.
  • A name can contain up to 255 characters.

Names can contain uppercase and lowercase letters. Microsoft Excel does not distinguish between uppercase and lowercase characters in names.

Logical Functions

A Logical function is a function that tests, or evaluates, whether a condition in the workbook is true or false. The condition is usually entered as an expression.

A Logical function is a function that tests, or evaluates, whether a condition in the workbook is true or false. The condition is usually entered as an expression.

A conditional test is an equation that compares two items. These items can be numbers, functions, formulae, labels or logical values. A conditional test must include at least one logical operator. The result of a conditional test is either TRUE or FALSE.

 

The IF function

Returns one value if a condition we specify evaluates to TRUE and another value if it evaluates to FALSE. Use IF to conduct conditional tests on values and formulas.

IF (logical_test, value_if_true, value_if_false)

Logical_test is any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE.

Value_if_true is the value that is returned if logical_test is TRUE. For example, if this argument is the text string “Within budget” and the logical_test argument evaluates to TRUE, then the IF function displays the text “Within budget”. If logical_test is TRUE and value_if_true is blank, this argument returns 0 (zero). To display the word TRUE, use the logical value TRUE for this argument. Value_if_true can be another formula.

Value_if_false is the value that is returned if logical_test is FALSE. For example, if this argument is the text string “Over budget” and the logical_test argument evaluates to FALSE, then the IF function displays the text “Over budget”. If logical_test is FALSE and value_if_false is omitted, (that is, after value_if_true, there is no comma), then the logical value FALSE is returned. If logical_test is FALSE and value_if_false is blank (that is, after value_if_true, there is a comma followed by the closing parenthesis), then the value 0 (zero) is returned. Value_if_false can be another formula.

Some points to be kept in mind while working with if function

  • Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests.
  • When the value_if_true and value_if_false arguments are evaluated, IF returns the value returned by those statements.
  • If any of the arguments to IF are arrays, every element of the array is evaluated when the IF statement is carried out.

Suppose we want to assign letter grades to numbers referenced by the name Average Score. See the following table. We can use the following nested IF function

If Average Score is Then return
Greater than 89 A
From 80 to 89 B
From 70 to 79 C
From 60 to 69 D
Less than 60 F

 

Nested IF statements

Sometimes your problem may be so involved that you cannot solve it by using just the techniques you have met so far. An extremely useful feature is the ability to nest IF functions (put one inside another). You can nest up to seven IF functions as long as the number of characters in your formula does not exceed the maximum number of characters that can be entered in a cell.

There is not a single general form but the following is typical:

=IF( condition1,value1,IF( condition2,value2,value3 ))

AND, OR and NOT

When building up a conditional test you sometimes need quite complicated conditions. These three functions can help to simplify the job. The functions AND and OR can take up to 30 logical arguments whereas NOT has only one argument.

General forms:

=AND(logical1,logical2,…,logical30 )

=OR(logical1,logical2,…,logical30 )

=NOT(logical1 )

The arguments for the functions can be

  • conditional tests
  • references to cells containing logical values
  • arrays of logical values

AND

  • If all the arguments in an AND function are true, the result is TRUE .
  • If one or more arguments are false, the result is FALSE.

OR

  • If at least one of the arguments in an OR function is true, the result is TRUE.
  • If all the arguments in an OR function are false, the result is FALSE.

NOT

  • This function negates a condition.
  • If the argument in a NOT function is false, the result is TRUE.
  • If the argument in a NOT function is true, the result is FALSE.

A few very simple examples follow

Formula Result
=AND(5>3,80<96) TRUE
=AND(2*24<67,7<0.34,4*4=2*8) FALSE
=OR(4*4=8*2,56>297) TRUE
=OR(2=3,3=4,4=5) FALSE
=NOT(5<3) TRUE
=NOT(6=6) FALSE

 

Function Description
AND Returns TRUE if all of its arguments are TRUE
FALSE Returns the logical value FALSE
IF Specifies a logical test to perform
IFERROR Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
NOT Reverses the logic of its argument
OR Returns TRUE if any argument is TRUE
TRUE Returns the logical value TRUE

Statistical functions

Excel provides a great deal of assistance when it comes to analysing statistical data. In addition, the Analysis ToolPak can be used, which is an add-in module with a collection of functions and tools that enable you to produce rank-and-percentile tables, perform regression analysis, apply Fourier transformations to data, and so on.

The COUNT Function

Counts the number of cells that contain numbers and numbers within the list of arguments. Its usage is

COUNT (value1, value2…)

Value1, value2, … are 1 to 30 arguments that can contain or refer to a variety of different types of data, but only numbers are counted.

Arguments that are numbers, dates, or text representations of numbers are counted; arguments that are error values or text that cannot be translated into numbers are ignored.

The MAX Function

Returns the largest value in a set of values. Its usage is

MAX (number1, number2…)

Number1, number2… are 1 to 30 numbers for which you want to find the maximum value.

You can specify arguments that are numbers, empty cells, logical values, or text representations of numbers.

The MIN Function

Returns the smallest number in a set of values. Its usage is

MIN (number1, number2…)

Number1, number2… are 1 to 30 numbers for which you want to find the minimum value.

You can specify arguments that are numbers, empty cells, logical values, or text representations of numbers. Arguments that are error values or text that cannot be translated into numbers cause errors. If the arguments contain no numbers, MIN returns 0.

The AVERAGE Function

In the above example we have calculated the average of the values lying between B3 cell to B6, with the formula

=Average (B3:B6)

Date and Time Functions

The TODAY() Function and the NOW() Function – The TODAY function returns the serial number of today’s date based on your system clock and does not include the time. The NOW function returns the serial number of today’s date and includes the time.

In Excel, dates are sorted based on the serial number of the date, instead of on the displayed number. Therefore, when you sort dates in Excel, you may not receive the results you expect.

Because serial numbers are also used in date and time comparisons, actual results may be different than what you expect (based on the displayed values).

Function Description
DATE Returns the serial number of a particular date
DATEVALUE Converts a date in the form of text to a serial number
DAY Converts a serial number to a day of the month
DAYS360 Calculates the number of days between two dates based on a 360-day year
EDATE Returns the serial number of the date that is the indicated number of months before or after the start date
EOMONTH Returns the serial number of the last day of the month before or after a specified number of months
HOUR Converts a serial number to an hour
MINUTE Converts a serial number to a minute
MONTH Converts a serial number to a month
NETWORKDAYS Returns the number of whole workdays between two dates
NOW Returns the serial number of the current date and time
SECOND Converts a serial number to a second
TIME Returns the serial number of a particular time
TIMEVALUE Converts a time in the form of text to a serial number
TODAY Returns the serial number of today’s date
WEEKDAY Converts a serial number to a day of the week
WEEKNUM Converts a serial number to a number representing where the week falls numerically with a year
WORKDAY Returns the serial number of the date before or after a specified number of workdays
YEAR Converts a serial number to a year
YEARFRAC Returns the year fraction representing the number of whole days between start_date and end_date

Lookup and Reference functions

Sometimes, there may be a requirement for a table in which a person might look up a value that corresponds to some search value. One example of this is the logarithm table. Another is a price list in which one looks up the price of a particular item.

In Excel this can be done with the various lookup functions. The most common is the Vlookup function that allows a person to find information listed in a vertical table. Hlookup is for use with horizontally organised data.

The VLOOKUP function

Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data you want to find. Its usage is

VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)

Lookup_value is the value to be found in the first column of the array. Lookup_value can be a value, a reference, or a text string.

Table_array is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List.

  • If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order…, -2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.
  • You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending.
  • The values in the first column of table_array can be text, numbers, or logical values.
  • Uppercase and lowercase text is equivalent.

Col_index_num is the column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is less than 1, VLOOKUP returns the #VALUE! error value; if col_index_num is greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

HLOOKUP Function

Searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array. Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find. The H in HLOOKUP stands for “Horizontal.” It’s syntax is as –

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

List of reference functions are:

Function Description
ADDRESS function Returns a reference as text to a single cell in a worksheet
AREAS function Returns the number of areas in a reference
CHOOSE function Chooses a value from a list of values
COLUMN function Returns the column number of a reference
COLUMNS function Returns the number of columns in a reference
FORMULATEXT function Returns the formula at the given reference as text
GETPIVOTDATA function Returns data stored in a PivotTable report
HLOOKUP function Looks in the top row of an array and returns the value of the indicated cell
HYPERLINK function Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet
INDEX function Uses an index to choose a value from a reference or array
INDIRECT function Returns a reference indicated by a text value
LOOKUP function Looks up values in a vector or array
MATCH function Looks up values in a reference or array
OFFSET function Returns a reference offset from a given reference
ROW function Returns the row number of a reference
ROWS function Returns the number of rows in a reference
RTD function Retrieves real-time data from a program that supports COM automation
TRANSPOSE function Returns the transpose of an array
VLOOKUP function Looks in the first column of an array and moves across the row to return the value of a cell

 

Lear and become- Certified Inventory and Warehouse Analytics Professional

Go back to Tutorial

Get industry recognized certification – Contact us

Menu