270 likes | 342 Views
Relative Addresses: . After I enter =B2/52 in cell C2 and then drag it down the C column to the last row what formula would I see in cell C5 if I clicked on it?. =B2/52 = B 2/55 = B 5/52 = B 5/55. Conditional/If Function: .
E N D
Relative Addresses: After I enter =B2/52 in cell C2 and then drag it down the C column to the last row what formula would I see in cell C5 if I clicked on it? =B2/52 =B2/55 =B5/52 =B5/55
Conditional/If Function: What would be the correct function to type into cell C2 to indicate either “passed” or “failed”. Assume anyone who earned more than 30 pts passed. =IF(B2>30,”passed”,”failed”) =IF(B2>=30,”passed”,”failed”) =IF(B2>30,”failed”,”passed”) =IF(B2>=30,”failed”, “passed”
The COUNT function counts the number of cells in a range with numerical data in them • What does the COUNTA function return in this case? =COUNTA(A2:C5) 4 8 12 None of the above
Relative AddressingCopy A2 into D2 and into B3:What formula would be in each?
Excel: Formulas and Functions • Data (and increasingly large amounts of it) permeates our society and our disciplines • Those who can leverage computational tools and techniques for analyzing it will be primed to make contributions • Excel is an amazingly powerful tool for • Data analysis • Data visualization • We’ll find a new level of “understanding” of many Excel tools given our understanding of core computing concepts gained in Alice
Working Example:CSE3 grades in Excel • Many profs keep track of grades in excel • Scantron form scanned, emailed as CSV (comma separated value), which is imported to Excel (chapter 3) • Each row has 2 entries* • Student PID • Number of points *Actually I get the direct scans and do my own grade calculations, so I can give partial credit… more later
An Example of a Common Organization • National Data Buoy Center: • Station 46410 (LLNR 984.6) - 330 NM Southeast of Anchorage, AK #YY MM DD hh mm ss T HEIGHT #yrmodyhrmn s - m 2010 11 18 12 00 00 1 3728.243 2010 11 18 11 45 00 1 3728.303 2010 11 18 11 30 00 1 3728.370 2010 11 18 11 15 00 1 3728.441 2010 11 18 11 00 00 1 3728.521 2010 11 18 10 45 00 1 3728.601 2010 11 18 10 30 00 1 3728.681
What BEST explains in English the “purpose” of this formula? • It generates the values 22, 33, 44 and 55 • It generates a value which is 11 less than the top value in that column • It generates a value which is 11 less than the value in the cell directly above it • None of the above
Absolute Reference:Uses in grading CSE3 midterms • Keeping the “total number of points” • Rather than “hard coding” the number 52 in the equation, it should always use the number in B2 • Easy for someone “looking” at the sheet to see the max points Let’s Change this
What would be the right formula: Given I want to be able to copy/drag it into ANY column on the sheet and get the grade for that row • =B3/B$1 • =B3/$B$1 • =$B3/B$1 • =$B3/$B$1 Let’s Change this
More CSE3 Midterm Analysis:How many people picked a specific distractor • Count of each item (A-E) in a column • I’ll enter into B6: = COUNTIF(B$2:B$4,=“A”) I want to drag it down over the next three rowsand then just change Ato B, or C or D (less typing)
= COUNTIF(B$2:B$4,”=A”) • In B6 Why do I use absolute addressing for the ROW? • Because you want to be able to easily drag this equation across all the columns (questions on exam) without having to change it • Because you want tomake sure it alwaysperforms COUNTAon rows 2-4 • I don’t know
In Excel, a relative reference (no $) (compared to an absolute reference (with $))… • Keeps the same row, column reference when you copy it into another cell • Changes the row, column reference when you copy it into another cell, based on the value in the original cell • Changes the row, column reference when you copy it into another cell, based on the location of the new cell compared to the old one
Absolute Addressing is useful for • When you want to always reference the same column, no matter where you copy it • When you want to always reference the same row, no matter where you copy it • When you always want to reference the same cell, no matter where you copy it • More than one of the above is true
Suppose I have two classes grades, in different sheets • I find the average in the first class (Sheet1: B5) • Then I’ll copy that over to (Sheet2: B4) so I’ll have the average grade for that class as well
What is in B4 in Sheet2? D. Excel gives an error E. None of the above • 39 • 50 • 78
Which of the following would cause a circular reference if I entered it in B2 and dragged it across row 2? • =B1+B3 • =A2+C2 • Neither of those • Both of those
Assume this function is put into I1 and then copied down to I4, =IF(H1="OK",G1+$F$1,G1-F1) • What is in Cell I3? • 2 • 5 • 9 • 15
Assume this function is put into I1 and then copied down to I4, =IF(H1="OK",G1+$F$1,G1-F1) • What is in Cell I4? • 2 • 5 • 12 • 15
What is the correct formula to use • For Question 1 • D was worth 2 points • C was worth 1 point • Others worth 0 points • =IF(B3=“D”,2), IF(B3=“C”,1,0)) • =IF(B3=“D”,2,IF(B3=“C”,1,0)) • =IF(B3=“C”,1,IF(B3=“D”,2,0)) • More than one of the above • None of the above
Match your PID (from scantron) to name (from classlist) • In one sheet, I have the midterm scanned data • I’ve used if statements to assign points per question and summed them to get exam points • In another sheet I have the downloaded class roster • (which usually has some people who didn’t take the exam – not the same number of rows) • It has PID and Name on each row • Goal: Add a column in midterm sheet with the matching name for each student
What commands would you use to fill A3? • An IF command • An IF command with nested IFs • A VLOOKUP command • A VLOOKUP command with an IF nested in it • A COUNTA function
To calculate A3= VLOOKUP(B3,classList!A$1:B$572,2,TRUE) For all items_in_classList_ColA one at a time If item_in_classList_ColA == midterm!B3 midterm!A3’s value is classList!B(index) else Do Nothing