Transcript - Sundara Ram MattA Jan 12th, 2014

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.