AGB 260: Agribusiness Information Technology

Advanced Functions and Logic

Useful Chapters in the Textbook Regarding this Lecture

Chapter 11

Chapter 13

Chapter 14

Some of the other functions in this chapter are scattered throughout the book.

Logical Functions in Excel

And(logical1,[logical2],…) This function checks to see if all of the logical

arguments are true.

If any one argument is false, the function returns false.

All of the arguments must be true for the function to return true.

Or(logical1,[logical2],…)

This function checks to see if any of the logical arguments are true.

If any one argument is true, the function returns true.

All of the arguments must be false for the function to return false.

Note for Logical Tests

Usually when you are doing a logical test that involves text, you must put the text into quotation marks, i.e., “ ”.

For example: And(A1=“Dec”) is an appropriate way to test if cell A1 has the letters Dec in it.

Quotation marks with nothing between them can denote an empty cell, i.e., “”.

Logical Functions in Excel Cont.

Not(logical)

This function takes a true statement and turns it false and false statement and turns it to true.

If(logical_test,[value_if_true],[value_if_false])

This function checks a statement to see if it is true.

If the statement is true, then the function will do what is in the [value_if_true] section of the function.

If the statement is false, then the function will do what is in the [value_if_false] section of the function.

You can nest up to 64 If statements in Excel 2013.

Diagramming Multi-Level If Statements

The idea of an If statement is taking one of two paths depending on whether the statement is true or false.

If you have embedded If statements, then you can have potentially many paths to keep track of.

Excel has the ability to create diagrams of If statements.

This tool is located under the Insert tab on the Smart Art button.

Example of a Diagram Depicting an If Statement

Statement

What to do if the

statement is true

What to do if the

statement is false

Example of a Diagram Depicting a Multi-Level If Statement

Statement

Statement evaluates to true

and creates a second statement

to test

What to do if statement 2 is true

What to do if statement 2 is

falseWhat to do if the statement is false

Quick Note on Diagrams

While Smart Art can be a useful tool for making hierarchical diagrams, sometimes the tool can be clumsy to use. In this case, you can use the

Shapes button on the Insert tab to make a more free flowing diagram.

Contingent Counting

Countif(range,criteria)

This function counts the set of cells in a range that meets some criteria.

Unless you are testing for equality, you need to put the criteria in quotation marks, e.g., =COUNTIF(B2:D4,">=5").

Countifs(criteria_range1,criteria1, …)

This function counts the set of cells in a range that meets a set of criterion.

This function allows for more than one criteria.

This function can be simulated with the Countif() function by using the And() and Or() functions.

Contingent Summing

Sumif(range,criteria,[sum_range])

This function sums the numbers in a set of cells in a range that meets some criteria.

The last argument is optional and is needed if the range for the criteria is different from the sum range.

Sumifs(sum_range,criteria_range1,criteria1,[criteria_range2], [criteria2], …)

This function sums the numbers in the set of cells in a range that meets a set of criterion.

This function allows for more than one criteria.

This function can be simulated with the Sumif() function by using the And() and Or() functions.

Contingent Averaging

Averageif(range,criteria,[average_range])

This function averages the numbers in a set of cells in a range that meets some criteria.

The last argument is optional and is needed if the range for the criteria is different from the average range.

Averageifs(average_range,criteria_range1,criteria1,[criteria_range2], [criteria2], …)

This function averages the numbers in the set of cells in a range that meets a set of criterion.

This function allows for more than one criteria.

This function can be simulated with the Averageif() function by using the And() and Or() functions.

In-Class Activity

Given the data in the LogicFunctions worksheet in the Lec3Examples2.xlsx workbook, answer the following questions with your logical operators and contingent functions: Using Countif() and the And() functions,

how many females are aged 40 or above? Verify your answer with the Countifs() function.

Verify this with the If(), And(), and Sum() functions. You may need to add a column to get this answer.

In-Class Activity Cont.

Using Countif() and the Or() functions, how many individuals are female or aged 40 or above?

Can you use a single Countifs() function to find this answer?

If no, what could you do to verify your answer?

Using Countif(), And(), and the Not() functions, how many individuals are not females who are aged 40 or above?

In-Class Activity Cont.

Using the Sum() function and the logical operators discussed in Lecture 2, how much total/aggregate income do the individuals who make over 75,000 have? Verify this with the Sumif() function.

Using the Sum() function and the logical operators discussed in Lecture 2, how much total/aggregate income do the male individuals who make over 75,000 have? Verify this with the Sumifs() function.

Using the Averageifs() function, what is the average income for males earning over 75,000?

Quick Note on Conditional Sums and Averages

To verify your answers, it is sometimes useful to use the filter button that is located on the Data Ribbon. Use the Filter tool to check your

answer for the question: how much total/aggregate income do the male individuals who make over 75,000 have?

Lookup Functions

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

This function looks for a value in the top row of a table and returns a value in the same column for a row you specify.

The first row needs to be sorted in ascending order.

Vlookup(lookup_value,table_array, col_index_num,[range_lookup])

This function looks for a value in the left most column of a table and returns a value in the same row for a column you specify.

The first column needs to be sorted in ascending order.

Lookup Functions Cont.

Index(array,row_num,col_num)

This function returns the value of the cell at the intersection of a particular row and column in a given range.

This function is useful when used with other functions.

Match(lookup_value,lookup_array,[match_type])

This function returns the position of an item in an array that matches a value in a specified order.

It has the optional argument of 0 for exact match, -1 for greater than, and 1 for less than the matching term.

In-Class Activity

Given the data in the LogicFunctions worksheet in the Lec3Examples2.xlsx workbook, do the following with your Lookup functions:

In row F, you have a number that represents a level of schooling where the number is deciphered from the information in cells A29:B34. In a new column, write a formula using a Lookup function that will transform the numbers into written education levels. Write an If() statement that will do the same.

Which is easier?

In-Class Activity Cont.

Given the data in the Lookup-Example2 worksheet in the Lec3Examples2.xlsx workbook, do the following with your Lookup functions: Use a horizontal lookup function to find what

number is in column 5, row 3. What number do you get?

Use a vertical lookup function to find what number is in column 5, row 3. What number do you get?

If you wanted to change each of these formulas so they give you the correct division result, what change(s) would you need to make?

In-Class Activity Cont.

Use the index function to lookup what is in the column 5 and row 3 of the table in A1:P16.

What change would you need to make to give the appropriate answer if you wanted the answer to the row divided by the column?

Write four different match functions that look up the row where 5 shows up in columns A, B, C, and D.

The Offset Function

Offset(reference,rows,cols,[height],[width]) This function returns a reference to a range that is given a

number of rows and columns from a given reference.

The offset function can be a useful function when you want to transform monthly tabular data into linear data.

The reference argument tells you what cell you want to start your offset from

The rows argument tell you how many rows you would like to change from your reference.

The cols argument tell you how many columns you would like to change from your reference.

The height argument tells you how many rows you are going to capture.

The width argument tells you how many columns you are going to capture.

Error Functions

Iferror(value,value_if_error) This function checks to see if there is an

error in a particular cell/value. If there is no error, then the function

returns a the value.

If there is an error, the function will return the value you specify.

Iserror(value) This function checks to see if the value is

an error and returns true or false.

The Aggregate Function

Aggregate(function_num,options,array,[k])

Aggregate(function_num,options,ref1,…) This function is a very powerful function

that has two different sets of arguments. This function can apply different

aggregate functions, e.g., Average(), Count(), Max(), etc., to a list or database and has the ability to ignore hidden rows and errors in cells.

Text Searching Functions

Search(find_text,within_text,[start_num]) This function searches for the first occurrence of

a character past the start number, which is optionally set to 1, inside of a given text reading left to right.

This function is not case sensitive.

Find(find_text,within_text,[start_num]) This function searches for the first occurrence of

a character past the start number, which is optionally set to 1, inside of a given text reading left to right.

This function is case sensitive.

Text Functions

Upper(text) Converts a text string to all uppercase letters.

Lower(text) Converts a text string to all lowercase letters.

Proper(text) Converts the first letter in each word that is part of

a string to uppercase and makes all the other letters lower case.

Mid(text,start_num,num_chars) This function extracts a certain number of

characters from a given text given a starting position and length.

Conversion and Indirect Functions

Convert(number,from_unit,to_unit) This function will convert a number

that is specified in one type of unit, e.g., feet, to another number that is in another unit, e.g., meters.

Indirect(ref_text,[a1]) This function returns the reference

specified by a text string.

In-Class Activity

Given the data in the OffsetExample1 worksheet in the Lec3Examples2.xlsx workbook, examine the following with offset:

Reference is A1, Rows =1, Cols =1, Height =1, Width =1

Reference is B2, Rows =1, Cols =1, Height =1, Width =1

Reference is B2, Rows =-1, Cols =1, Height =1, Width =1

Reference is B2, Rows =-1, Cols =-1, Height =1, Width =1

Reference is A1, Rows =1, Cols =1, Height =1, Width =2

Reference is A1, Rows =1, Cols =1, Height =2, Width =1

Reference is A1, Rows =1, Cols =1, Height =2, Width =1

In-Class Activity Cont.

Given the data in the OffsetExample2 worksheet in the Lec3Examples2.xlsx workbook, create a function that will take the tabular data for prices and turns it into prices being in just one column. Using the Mod() and Row() Functions to create a

cycle of numbers from 1 through 12 that goes from A17 to A173.

Write an if function that generates the correct year starting with 2001.

Use a lookup function to generate the months.

Use an Offset() function to put the prices in column form.

In-Class Activity Cont.

Given the data in the ErrorAndTextExample worksheet in the Lec3Examples2.xlsx workbook, investigate the following: In column B, calculate 12 divided by the number in

Column A.

In column C, do the same as above except use the Iferror() function to make any error that might occur from the division into the word “Undefined”.

In Column D, write an If statement using the Iserror() function to do the same as the previous.

In column B, sum up the values. What do you get?

In Column B, use the Aggregate() function to do the Sum() and ignore the errors.

In-Class Activity Cont.

Use the Search() function in cell B17, to tell you where the first occurrence is of the letter t and T for the text in A14.

Use the Match() function in cell B18, to tell you where the first occurrence is of the letter t and T for the text in A14.

Examine the Uppercase, Lowercase, Proper Case, Mid, and Convert Examples.