Transcript AGB 260: Agribusiness Information Technology

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 evaluates
to true and creates a
second statement to
test
Statement
What to do if the
statement is false
What to do if
statement 2 is true
What to do if
statement 2 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_ra
nge2], [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,[crit
eria_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?


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 Sumif() function.
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)


Lower(text)


Converts a text string to all lowercase letters.
Proper(text)


Converts a text string to all uppercase letters.
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.