Vocab
This function counts the number of cells that fulfill a certain criteria.
=countif()
Write a function that will count the number of cells in range B2:B26 that contain a value greater than of equal to 15.
=countif(B2:B26, ">=15")
What are the 3 inputs in an IF function?
=if( logical test, value if true, value if false)
Working in Row 2: If the value in column A is 500 or less, display "Low Sales", otherwise "High Sales"
=if(A2<=500, "Low Sales", "High Sales")
What does the following function display if A1 = 10 and B1 = No?
=AND(A1>10, B1= "No")
FALSE
This function adds the cells in a range that fulfill a certain criteria.
=sumif()
Write a function that adds the cells in G3 to G14 if the cell in the same range in column B says "Good"
=sumif(B3:B14, "Good", G3:G14)
What function is used in an if function that can include multiple conditions but all need to be true?
=and()
Working in Row 5: If the value in column C is 22 or more and the value in column B says "Yes", display "Below Average", otherwise "Above Average"
=if(and(c5>=22, b5= "Yes"), "Below Average", "Above Average")
What does the following function display if A1 = 10 and B1 = Yes?
=OR(A1>=10, B1= "No")
TRUE
This function find the mean of all the cells in a range that fulfill a certain criteria.
-averageif()
What does the first range do in SUMIF and AVERAGEIF?
It is the range where the condition is found.
What function is used in an if function that can include multiple conditions but only 1 needs to be true?
=or()
Working in Row 7: If the value in column D says "Math" or column H says "Science", display "STEM Nerd", otherwise "Other Nerd"
=if(or(d7="Math", h7 = "Science"), "STEM Nerd", "Other Nerd")
When is Ms. C's birthday?
TODAY! 3/17
This function displays a certain value or text if a logical test is found to be true, and another value otherwise.
=if()
Write a function that adds the cells in C1 to C10 if the cells in the same range in column D that are 52 or greater.
=sumif(d1:d10, ">=52", c1:c10)
Write a function, if the value in a2 is greater than 10, display "More than 10", otherwise "10 or Less".
=if(A2>10, "More than 10", "10 or Less")
Working in Row 3: If the value in column A is 13 and or the value in column E is less than 300 or the value in column B says "No", display "On Probation", otherwise "On track".
=if(or(a3=13, E3<300, B3="No"), "On Probation", "On Track")
Working in Row 1: If the value in column N says "Yes" and the value in G says either "Great" or "Perfect", display "Great Student", otherwise "Needs Improvement".
=if(and(or(G1= "Great", G1= "Perfect"), N1= "Yes"), "Great Student", "Needs Improvement")
This is the thing that needs to be satisfied in any if function.
Criteria, condition, or logical test
Write a function that find the mean of the cells in A10 to A25 if the cells in the same range in column H that say "Yes".
=averageif(H10:H25, "Yes", "A10: A25)
Write a function, if the value in H7 says "Gym Goer", display "Active", otherwise "Not Active".
=if(h7= "Gym Goer", "Active", "Not Active")
Working in Row 1: If the value in column N says "Yes" andthe value in G says "Great" and column C says "Perfect", display "Model Student", otherwise "Needs Improvement".
=if(or(N1= "Yes", G1= "Great", C1 = "Perfect"), "Model Student", "Needs Improvement")
Working in Row 3: If the value in column A is 13 and the value in column C is less than 15, or the value in column E says "Not Completed", display "On Probation", otherwise "On track".
=if(or(and(A3=13, C3<15), e3 = "Not Completed"), "On Probation", "On Track")