AGB 260: Agribusiness Information Technology

1. AGB 260: Agribusiness Information Technology Basic Operators and Functions

2. Useful Chapters in the Textbook Regarding this Lecture • Chapter 6 • Chapter 7 • Chapter 13 • Many of the other functions in this chapter are scattered throughout the book.

3. Formulas in Excel and Basic Operators • Whenever you are starting a function or a formula in Excel by typing it either into the formula bar or directly into the cell, you need to put =, -, or + sign in front of it. • While you can use any of the three signs, it is recommended that you use the =. • Basic operators in Excel: • Logical: =, >, <, >=, <=, <> • Mathematical Operators: +, -,*,/, ^ (for exponents), • Concatenation Operator: &

4. Operator Precedence • ( ) • Anything in parenthesis is evaluated first • *, / • +, - • & • =, >,<, >=, >=

5. Formula Examples • =1+2+3, =5*(4/2), =10^10 • =1=1, =(1=1)*5, =1=1*5 • =5&5, =A&2, =“a”&2 • =a1+A2, +A$1+$B$1

6. Basic Functions in Excel • A function in Excel is a preprogrammed formula that contains a set of arguments • A function can have no arguments, e.g., =Rand() • one argument, e.g., =Sqrt(Value) • a fixed number of arguments, e.g., =if(Condition,True,False) • an indeterminate number of arguments, e.g., =sum(Value 1, Value 2, …) • and/or optional arguments e.g., =PMT(rate,nper,PV,[fv],[type]. • Arguments are typically separated by commas. • Whenever an argument is optional, it is placed in brackets.

7. Quick Note on Arguments using Colon (:) • Suppose you had the following function: • =Sum(A1,A2,a3,a4,a5) • Another way of writing this is to put: • =sum(a1:a5) • Putting a colon between A1 and a5 tells excel to use cells a1 through a5. • A1:a5 is known as a range of cells. • Suppose you had the following function: • =Average(A1,A2,b1,b2) • Another way of writing this is to put: • =average(a1:b2)

8. Useful Functions for Initial Data Analysis • =Count(value1,[value2],…) • This counts the number of cells in a range that are numbers. • =CountA(value1,[value2],…) • This counts the number of cells in a range that are not empty. • =CountBlank(value1,[value2],…) • This counts the number of cells in a range that are empty. • =Sum(value1,[value2],…) • Adds all the numbers in a range of cells. • =sumproduct(array1,[array2],[array3],…) • Returns the sum of the products.

9. Useful Functions for Initial Data Analysis Cont. • =Max(Number1,[Number2],…) • Gives you the largest number in a range of numbers while ignoring logical values and text. • =Large(Array,K) • Provides the K-th largest value in a dataset which can be known as an array. • =Min(Number1,[Number2],…) • Gives you the Smallest number in a range of numbers while ignoring logical values and text. • =Small(Array,K) • Provides the K-thsmallest value in a dataset which can be known as an array.

10. Useful Functions for Initial Data Analysis Cont. • =Rank.Avg(Number,Ref,[Order]) • Gives you the ranking of a number from a set of reference numbers where if two or more numbers tie then it will average their rankings. • Order is an optional argument set to 0 if you want the ranking in terms of descending order and 1 if you want the ascending order. • =Rank.EQ(Number,Ref,[Order]) • Gives you the ranking of a number from a set of reference numbers where if two or more numbers tie then it will give them the same ranking. • Order is an optional argument. • =Rank.EQ(Number,Ref,[Order]) • This is a backwards compatible function for Excel 2007 and before and works like =Rank.EQ(Number,Ref,[Order]).

11. Useful Functions for Initial Data Analysis Cont. • =Average(value1,[value2],…) • Gives the average of the range of cells. • =Median(value1,[value2],…) • Gives the median of the range of cells. • =SQrt(Number) • Returns the square root of a number. • =concatenate(text1,[text2],…) • Joins text strings together. • You can & in much the same way.

12. Useful Functions for Initial Data Analysis Cont. • =today() • Returns the current date. • =day(serial_Number) • Returns the day of the month from a given date. • =Month(serial_Number) • Returns the month from a given date in terms of a number from 1 to 12. • =Year(serial_Number) • Returns the Year from a given date. • =date(year,month,day) • Returns the number that represents a given date.

13. Useful Functions for Initial Data Analysis Cont. • =weekday(serial_number,[return_type]) • This returns a number for the day of the week, which can be customized for what the number means, e.g., 1=Sunday, 2 =Monday, etc. • =Networkdays(start_Date,End_Date,[holidays]) • Provides you with the number of workdays between to dates with the ability to customize the holidays.

14. Useful Functions for Initial Data Analysis Cont. • =mod(Number,divisor) • Returns the remainder from dividing a number by a divisor. • How could this be useful to you? • =round(number,num_digits) • Rounds a number to a specified number of digits. • =roundup(number,num_digits) • Rounds a number to a specified number of digits away from zero. • =rounddown(number,num_digits) • Rounds a number to a specified number of digits towards zero.

15. Useful Functions for Initial Data Analysis Cont. • =Len(text) • This counts the number of characters in a text string. • =Left(text,[num_chars]) • Returns a designated number of characters from the beginning of a text string, with the default being one. • =Right(text,[num_chars]) • Returns a designated number of characters from the End of a text string, with the default being one.

16. Useful Functions for Initial Data Analysis Cont. • =value(text) • Changes text to a number value. • =Text(value,[format_Text]) • This takes a value and formats in a particular style of text. • =row([reference]) • Returns the row number of a designated cell. • If it is left empty, then it gives the row number that the formula is in. • =Column([reference]) • Returns the column number of a designated cell. • If it is left empty, then it gives the row number that the formula is in.

17. Investigate Functions Using Carrier survey • Open carriersurvey-1-110310-Classexample.xlsx • In class we will work through examining this survey using the functions discussed above.