Transcript - Sundara Ram MattA Jan 12th, 2014

Day 5
- Sundara Ram Matta
[email protected]
Jan 28th, 2014
1
IMPORTANT ANNOUNCEMENTS !!!
• Homework #1 has been posted and due by 2/6/2015.
• Homework #2 will be also posted by Friday.
• All the MyITLab/ HW’s are due by 11:59 pm on the
due date specified.
2
ATTENDANCE
• Register your attendance only after 09:25 AM Section 3
and 10:25 AM Section 5.
• As few students have recorded their afternoon before
09:25 AM/ 10:25 AM and it will display in my records as
‘Wrong Time’.
• Even if you arrive early, look at the TIME in the computer
and login after 09:25 AM/ 10:25 AM.
3
MYITLAB
• PRE and POST TEST. Credit is only for the POST TEST.
• POST TEST – It is good if you have completed 100%.
• But even with 95% completion, that is one question being
unanswered, it should be good.
• You must complete at least 190 questions during
semester out of available 215 - 220 questions.
• Of course you can gain extra bonus when you answer
more than 190 questions.
• Works best in ‘Google Chrome’.
4
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/
5
IN-CLASS PROJECT
• Everyone should submit their files after completion, even if
working in a group.
• Submit your files, select the correct name for the submission.
Homework #1, Participation Project, Exams.
https://cs101.wvu.edu/tools/submit-assignments/’
• Submission should be done from the computers in the class
and before ending of the class.
• Remember out of 12, you must complete ‘10’ in-class
projects.
6
TILL NOW…
• Cell and Cell addresses
• Formulas (start with = sign)
• Cell references
– Relative B2
– Absolute $B$2
(When we require to make a value constant)
– Mixed
Row mixed reference B$2
Column mixed reference $B2
• Workbook/ Formatting/ Print Options/ Importing
Data
7
FUNCTIONS
FUNCTION BASICS
• Function – In excel it is a predefined computation that
simplifies creating a formula.
Ex: SUM instead of entering =B1 + B2 + B3 +….
• Excel contains more than 400 functions which are
organized into different categories.
 Date and Time
 Financial
 Logical
 Lookup & Reference
 Math & Trig
 Statistical
9
FUNCTION BASICS
• Usually the function name describes the purpose.
Ex: SUM function – Adds up the values.
AVERAGE function – To calculate the average
• Syntax – Starts with an equal = sign
• A function requires arguments which are the values, cell
addresses.
• Two ways of inserting functions in excel
10
INSERTING A FUNCTION
INSERTING A FUNCTION
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
SUM FUNCTION
• Totals the values and displays the result in
the cell where we entered the function.
• More efficient when we add values in three
or more cells
• =SUM (A2:A14) (No need of space after equal sign)
AVERAGE AND MEDIAN FUNCTION
• Calculates the central tendency i.e., values
tend to cluster around a central values.
• The AVERAGE function calculates the
arithmetic mean or the average for the range
of cells.
• =AVERAGE (A2:A14) (No need of space after equal
sign)
• The MEDIAN function finds the mid-point
value and more reflective of the data set.
• =MEDIAN (A2:A14)
MIN AND MAX FUNCTION
• Identifies the Low and High Values.
• Manually inspecting a sheet is inefficient
especially if data is large.
• =MIN (A2:A14)
• =MAX(A2:A14)
COUNT FUNCTION
• Identify the Total Number with COUNT.
• We have three variations:
– COUNT – excludes blank or 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
DATE FUNCTIONS
• The TODAY function displays the current date.
• Excel automatically updates it when you open it.
• Doesn’t require arguments, but you need to enter as
=TODAY()
• The NOW function used the computer’s clock to display
the date and time.
• =NOW()
• Omitting the parenthesis creates a #NAME? error.
LOGICAL FUNCTIONS
• A lot of work in Excel involves comparing data in
different cells.
• Logical functions test the Logic of situation and
return a particular result.
A
???
B
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
NEXT CLASS
• More functions VLOOK UP, PMT etc.
• Range names