Day 6 - Sundara Ram Matta [email protected] Feb 02nd, 2015 1 IMPORTANT ANNOUNCEMENTS !!! • Homework #1 is due by 2/6/2015 (this Friday). • Homework #2 has been posted and due by 2/13/2015. • Start working on MyITLab: Lesson B, due by 2/9/2015. • All the MyITLab/ HW’s are due by 11:59 pm on the due date specified. • Record your attendance after 09:25 am (Section 3)/ 10:25 am (Section 5). 2 OPEN LAB • 219 Armstrong Hall Monday 03:30 pm to 09:30 pm Tue/ Wed/ Thu: 03:30 pm to 05:30 pm Fri: 10:00 am to 06:00 pm • G06 Lyon Tower (ERC – Towers) Wed: 07:30 pm to 09:30 pm • Open Lab online chat Sun/ Tue/ Thu: 06:30 pm to 09:30 pm http://cs101.wvu.edu/resources/help/open-lab-online/ 3 GRADEBOOK • Under the course tools, you will find the option to view your grades. • The points or the grades do not appear instantaneously. • After the submission deadline, it will take time to process the MyITLab questions.. 4 TILL NOW… • Cell, Cell addresses and Cell References • Formulas/ Function (start with = sign) • Workbook/ Formatting/ Print Options/ Importing Data • Basic Math and Statistical functions • Date and time functions • Logical functions (IF) 5 BASIC MATH AND STAT FUNCTIONS • SUM - Total amount spent on dining out • AVERAGE - Average amount spent on iTunes to downloading music • MIN - Lowest time to run a mile this week • MAX - Highest electric bill in a year • COUNT - # of people attend an event • MEDIAN - Same as average COUNT FUNCTION • Identify the Total Number with COUNT. • We have three variations: – COUNT – excludes blank and N/A cells – COUNTBLANK – counts blank cells – COUNTA – excludes only blank cells. BASIC MATH AND STAT FUNCTIONS DATE FUNCTIONS • Today • Now • Date • Day • Edate • Eomonth • Month • Year LOGICAL FUNCTIONS • • • • • • Is value A equal to value B (A=B) Is A greater than B (A>B) Is A less than B (A<B) Is A greater than or equal to B (A>=B) Is A less than or equal to B (A<=B) Is A not equal to B (A<>B) • These are called Logical or Boolean operators because there can only be two possible answers in any given case - TRUE or FALSE. LOGICAL ‘IF’ FUNCTIONS • Most common logical function is the IF function. • The function returns one value when a condition is met (TRUE) or returns another value when condition is not met (FALSE). • =IF(Logical_Test , Value 1, Value 2) LOGICAL ‘IF’ FUNCTIONS RANK FUNCTION • The RANK function returns the rank of a number in a list of numbers. • Return value is a number that indicates rank. • = RANK (number, range, [order]) – number - The number to rank. – Range – Group of cells that contains the numbers to rank against. – Order - [optional] Whether to rank in ascending or descending order. FINANCIAL FUNCTIONS - THE PMT • The Excel PMT (payment) Function is a really simple to use but highly useful Financial Function used to calculate the repayment amount on a loan. • Suppose you purchased a car and the details – – – – Total Cost: 30,000 $ Initial Down payment: 4,000 $ Rate of Interest (Annual): 6% No: of years: 5 FINANCIAL FUNCTIONS - THE PMT • Need to know how much I need to pay per month…? • =PMT (rate, nper, pv, [fv]. [type]) – Rate (Annual): 6 % and No: of years: 5 – But the rate and nper is per month in the syntax • So Rate = 6% divided by 12 = 0.5% per month • No: of years (in months) = 5 x 12 = 60 months. NEXT CLASS • VLOOKUP function and wrap up the functions. • Basics of Charts.