1 / 25

Excel IF & VLOOKUP

Excel IF & VLOOKUP. The IF function. The IF function is a logical function used for making decisions based on some condition(s) Conditions have to result in Boolean values. (relational expressions, true/false ). The IF function syntax. = IF(Test , value_if_true,value_if_false) Test

tegan
Télécharger la présentation

Excel IF & VLOOKUP

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. Excel IF & VLOOKUP

  2. The IF function • The IF function is a logical function used for making decisions based on some condition(s) • Conditions have to result in Boolean values. (relational expressions, true/false)

  3. The IF function syntax =IF(Test, value_if_true,value_if_false) • Test • Condition that can be evaluated to true or false • Question that is answered either true or false • Value_if_true • Action to perform if test is true • Value_if_false • Action to perform if test is false

  4. GradeBook1 File 1-Excel Training Part 2

  5. Gradebook1!I3: Display, “Pass” if student passed the class or “Fail” if student did not pass. A passing score for this class is 245. Copy the formula for each student in the list.

  6. GradeBook2 File 1-Excel Training Part 2

  7. Gradebook2!H3: Calculate curved grade for students • Score Less than 150--Curve 10% • Score Greater than or Equal to150--Curve grade .05%

  8. Gradebook2!H3: Calculate curved grade for students • Score Less than 150--Curve 10% • Score Greater than or Equal to150--Curve grade .05%

  9. Gradebook2!K3: Calculate student’s letter grade

  10. Reference Functions • Functions that allow you to lookup a value in a list • Reference functions we will use • VLOOKUP • HLOOKUP

  11. Lookup Functions • VLOOKUP-List is set up in columns • HLOOKUP-List is set up in Rows

  12. =VLOOKUP(lookup_value,table_array, col_index_num, range_lookup) lookup_value criteria to lookup or “match” table_array The list col_index_num the column number in your range where to find the corresponding data range_lookup the lookup type TRUE ( or blank…Default) Looks for a match by finding the greatest value that does not exceed the criteria The lookup value cannot be smaller than the first value in the list The first column/row in the list must be in ascending order FALSE Function only looks for exact matches of the criteria or returns N/A#

  13. GradeBook3 File 1-Excel Training Part 2

  14. Gradebook2!K3: Calculate student’s letter grade

  15. Gradebook2!K3: Calculate student’s letter grade

  16. And/Or Function AND All items must be true for the statement to be true =AND(logical1, logical2,…) OR At least one item must be true for the statement to be true =OR(logical1, logical2,…)

  17. OSUN Admissions File 1-Excel Training Part 2

  18. OSUN Admissions!H3: Invalid Scores? True or False?

  19. OSUN Admissions!I3: • All Placement Scores < 500? True or False?

  20. OSUN Admissions!J3: • Math Placement Score > Average of all Math Placement Scores? True or False?

  21. OSUN Admissions!K3 • Foreign Language Score or English Score> 550? True or False?

  22. OSUN Admissions!L3 • Student in top 20% of class? True or False?

  23. OSUN Admissions!M3: • Admission Status? Accept, Waitlist, Reject

  24. OSUN Admissions!N3: Scholarship Candidate?

  25. OSUN Admissions!C8: • Average Placement Scores Compared to Last Year? • Higher, Same, Lower • (Last years average score: 610)

More Related