1 / 17

AGB 260: Agribusiness Information Technology

AGB 260: Agribusiness Information Technology. Basic Operators and Functions. 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. Formulas in Excel and Basic Operators.

alder
Download Presentation

AGB 260: Agribusiness Information Technology

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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.

More Related