Conditional and CrossSheet Formulas William Klingelsmith Announcements Homework

Conditional and Cross-Sheet Formulas William Klingelsmith

Announcements • Homework 2 (Healthcare Expenditures Problem) due this Friday (2/15/13) by 11: 59 pm • Homework 3 due 2/22/13 by 11: 59 pm

Homework 1 Review • Grades overall were very good • Submission rates were high • Any questions about your grade? Ask after class or during open lab this week

Cheating Cases • There was one instance of cheating in both sections • Remember that cheating twice will automatically earn you an F for the class • Protip: when copying files from your friends, remember to remove their name from the very first cell in the worksheet

Homework 1 Common Errors • =RANK() function • If you used the pointing method to construct your formula, you may ignore rant • =RANK(B 4, B$4: B$336) • =RANK(B 4, $B$4: $B$336) also acceptable • Average duration formula • =(D 4 -E 4)/F 4 • Remember that the order of operations matters! • Using AVERAGE in Miles Per Day and Passengers Per Day • Unnecessary because the ‘averaging’ is done by the division • Points were not taken off this time

Good job! • One student had a unique solution to the two formulas on the Usage worksheet • =IF(E 4=0, " ", B 4/E 4) • Prevents the division by zero errors

Conditional Formulas • Excel has a set of formulas which implement logical tests to narrow down results • One you may have seen thus far is the =IF() statement

=IF() Statement • The IF function lets you perform one calculation or another depending on a specific condition • The =IF() function has three arguments • Logical_test: the defined condition • Value_if_true: what will happen if the logical test is true • Value_if_false: what will happen if the logical test is false

=IF() Statement • On the band worksheet, insert a new column called ‘SA Good/Bad’ to the right of Scholarship Amount • In cell I 2, enter the formula • =IF(H 2>=4500, "Good", "Bad") • The above formula works as follows: • Take the value (H 2) and compare it to the logical condition • If the logical test is true, do the second argument (Place the word “Good” in the cell) • If the logical test is false, do the third argument(Place the word “Bad” in the cell)

=IF() Statement >4500 “Good” “Bad”

=IF() Statement • All of the familiar comparison operators from mathematics can be used in the logical test of an IF statement (=, <, >, >=, <=) • To construct a logical test using textual data, you will need to use quotation marks • Example: =IF(B 2=“M”, “Boy”, “Girl”) • Create a new column called ‘Graduating Soon? ’ • If the student is a senior, have an IF statement output the word Yes, else output No

Multiple Conditions • There will be instances where one logical test is insufficient • Let’s say we wanted to highlight which band members were women who play the flute • By embedding an AND() function in the logical test of our IF statement, we can test for multiple conditions

AND() Function • The AND function is an aggregation of several logical tests • All logical tests must be true for the AND function to return true • Insert a column to the left of ‘Sex’ and name it ‘Female Flautist? ’ • In B 2, enter the formula: • =IF(AND(C 2="F", G 2="Flute"), "X", "")

Statistical Conditional Functions • We have learned about basic stat. functions such as AVERAGE, SUM, MIN, MAX, etc. • Excel has functions which combine the above functions with the power of an IF statement • All of these functions have similar syntax: • Range: the cell range which holds the criteria • Criteria: the condition we’re trying to satisfy • [function_range]: the cell range the function will execute on

Statistical Conditional Functions • Let’s say we’re trying to find the average scholarship amount of women only • In cell A 67, type “F Amount” • In cell B 67, type the formula: • =AVERAGEIF(C 2: C 65, “F”, I 2: I 65)

=COUNTIF() • We’re now interested in counting up the number of students with a height of six feet or more • In cell A 68 enter the label “Tall Students” • In cell B 68 enter the formula: • =COUNTIF(D 2: D 65, 6)

Plural Versions • Each of these functions has a counterpart which allows multiple conditions to be used • • AVERAGEIFS SUMIFS COUNTIFS Etc.

Cross Sheet Formulas • Excel has the capability of using data from one worksheet in functions on another worksheet • The syntax takes the form of: • =SUM(‘Sheet Name’!<Cell Range>) • The easiest way to set up these formulas is to simply select them