1 / 27

Relative Addresses:

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

clem
Download Presentation

Relative Addresses:

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

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

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

  4. Relative AddressingCopy A2 into D2 and into B3:What formula would be in each?

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

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

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

  8. Faked: Dental Data

  9. Faked: UCSD Class Lists

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

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

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

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

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

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

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

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

  18. What is in B4 in Sheet2? D. Excel gives an error E. None of the above • 39 • 50 • 78

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

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

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

  22. Assigning points with If commands

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

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

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

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

More Related