1 / 18

Conditional and Cross-Sheet Formulas

Conditional and Cross-Sheet Formulas. William Klingelsmith. Announcements. Homework 2 (Healthcare Expenditures Problem) due this Friday (2/15/13) by 11:59pm Homework 3 due 2/22/13 by 11:59pm. Homework 1 Review. Grades overall were very good Submission rates were high

waseemah-duaa
Download Presentation

Conditional and Cross-Sheet Formulas

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. Conditional and Cross-Sheet Formulas William Klingelsmith

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

  3. 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

  4. 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

  5. Homework 1 Common Errors • =RANK() function • If you used the pointing method to construct your formula, you may ignore rant • =RANK(B4,B$4:B$336) • =RANK(B4,$B$4:$B$336) also acceptable • Average duration formula • =(D4-E4)/F4 • 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

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

  7. 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

  8. =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

  9. =IF() Statement • On the band worksheet, insert a new column called ‘SA Good/Bad’ to the right of Scholarship Amount • In cell I2, enter the formula • =IF(H2>=4500, "Good", "Bad") • The above formula works as follows: • Take the value (H2) 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)

  10. =IF() Statement

  11. =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(B2=“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

  12. 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

  13. 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 B2, enter the formula: • =IF(AND(C2="F", G2="Flute"), "X", "")

  14. 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

  15. Statistical Conditional Functions • Let’s say we’re trying to find the average scholarship amount of women only • In cell A67, type “F Amount” • In cell B67, type the formula: • =AVERAGEIF(C2:C65, “F”, I2:I65)

  16. =COUNTIF() • We’re now interested in counting up the number of students with a height of six feet or more • In cell A68 enter the label “Tall Students” • In cell B68 enter the formula: • =COUNTIF(D2:D65, 6)

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

  18. 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

More Related