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

  wrap text feature to create a conditional function that is wrapped

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

  formatting and insertion formulas to create conditions

  plus, minus and * operators to create these formulas

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

  highlights

  deletes

  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

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

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

  Change the "less than" to "equal to"

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

 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)

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

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

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

 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. >1 2. <100

  1.<50 2. =>50

  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

  rested

  tested

  infested

 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 best value

  represents a lower value

  represents a higher value

  represents the colour's numerical value

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

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

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

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

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

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

  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)

  None of these answers are valid

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

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

  Determines whether A2+A3 is less than the number 24

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

  Determines whether either A2 or A3 contains the number 24


Report an error