Preview

09 - Conditional Logic in Excel

 1. Testing whether conditions are true or false and making logical comparisons between expressions are common to many tasks. You can use the ____________________.

  formatting and insertion formulas to create conditions

  AND, OR, NOT, and IF functions to create conditional formulas

  plus, minus and * operators to create these formulas

  wrap text feature to create a conditional function that is wrapped

 2. Conditional formatting quickly ____________ important information in a spreadsheet. But sometimes the built-in formatting rules don’t go quite far enough.

  deletes

  highlights

  suggests

  indexes

 3. If we wished for every cell that has a cell value of 101 to infinite to be highlighted in red, what would change about this rule?
Conditional_formatting_formatting_rule1.png

  Rules can only be "less than", so it would not be possible to do this

  the "less than" would be changed to "greater than"

  Change "Format only cells that contain" to "Format all cells that contain 101-infinite"

  Change the "less than" to "equal to"

 4. Analyse the following image. What conditional logic formula has been applied to make the cells say "Fail" or "Pass" depending on the condition.
passing_score_conditional_function.png

  Answer: =IF(C6>=70,"Pass","Fail")

  Answer: =IF(C1>=<70,"Pass","Fail")

  Answer: =IF(C6=NOT 70,"Pass","Fail")

  Answer: =IF(C6<=70,"Pass)

 5. Fill in the blanks for 1 and 2, referring to the image below. What rules are in place?
Conditional_formatting_formatting_rule2.png

  1.<50 2. =>50

  1. >1 2. <100

  1. =50 2. <100

  1. >=50 2. <50

 6. The IF function can be "_____". A "_____" IF refers to a formula where at least one IF function is _____ inside another in order to test for more conditions and return more possible results.

  nested

  tested

  infested

  rested

 7. Data bars in Excel make it very easy to visualize values in a range of cells. A longer bar __________________________.
With conditional formatting tools, it is also possible to create colour scales  and data bars.
colour_scales_and_databars.png

  represents the colour's numerical value

  represents the best value

  represents a lower value

  represents a higher value

 8. Select the description that matches the following formula.
Formula: =IF(A2=15, "OK", "Not OK")

  If the value in the cell adds up to 15, then print "OK, NOT OK" together

  If the value in cell A2 equals 15, return "OK." Otherwise, return "Not OK." (OK)

  If the value in cell A2 is NOT equal to "OK", then return the word "NOT OK"

  If the values in the first 15 cells starting from A2 are OK (e.g. contain numbers) then print "OK"

 9. Select the description that matches the following formula.
Formula:  =AND(A2>A3, A2<A4)

  None of these answers are valid

  Determines if both A2 and A3 are greater, when added, than A4

  Determines if the value in any cell is greater than A2 and A4

  Determines if the value in cell A2 is greater than the value in A3 and also if the value in A2 is less than the value in A4. (FALSE)

 10. Select the description that matches the following formula.
Formula: =NOT(A2+A3=24)

  Determines whether A2+A3 is less than the number 24

  Determines whether either A2 or A3 contains the number 24

  Determines if the sum of the values in cells A2 and A3 is not equal to 24. (FALSE)

  Determines if the number 24 is NOT greater than either A2 or A3