WorksheetsSpreadsheet - Advanced Functions and Formulae
Total questions: 20
Worksheet time: 35mins
This function returns the number of cells that meet a condition set forth in a formula
What function will produce results that indicate whether a student has passed or failed a course by entering the text "Pass" or "Fail"
Count
Countif
If
Vlookup
Which function would display "Yes" if D13 is greater than 0?
=IF(D13>0, "Yes", "No")
=IF(D13<0, "Yes", "No")
=IF(D13>0: "Yes", "No")
=IF(D13<0: "Yes", "No")
You are creating a spreadsheet that contains Community Service Hours goals. Column A lists the number of community service hours. Based on the value in column A, column B indicates if the community service hours goal of 500 hrs. was met. Choose the formula that will calculate if the goal was achieved.
=IF(A1>=500, "Yes","No")
A. =IF(A1>=500,[Yes", "No"])
A. =IF(A1>=500,(Yes), (No))
A. =IF[A1>=500, Yes, No]
The countif function uses two (2) arguments
False
True
The first argument in the CountIF function is range
True
False
One of these function is not a valid Countif function
=countif(A2:A5,"h")
=countif(A2:B5,"h")
=countif(A2:A5,"<10")
=countif(A2:A5,">7")
Which one of these functions gives the result shown in the picture?
=COUNTIF(D5:D13,"<15")
=COUNTIF(D5:D13,"=16")
=COUNTIF(D5:D13,">=10")
=COUNTIF(D5:D13,">=15")
The IF function makes use of something called __________ which is used to form the logical test
functional operators
relational operators
relational database
absolute reference
Which of the following is the correct formula for the IF function in Excel?
IF(logical_test, value_if_true, value_if_false)
=IF (logical_test, value_if_true, value_if_false)
=(logical_test, value_if_true, value_if_false)
=IF(range, value_if_true, value_if_false)
If the cell B2 contains the value 68, what will this IF function give you as a result? =IF (B2>60, "pass", "fail")
pass
fail
60
ERROR
=IF(B5>B4, "Large", "Small")
The value in B5 = 56
The value in B4 = 89
What is the result that this IF statement will return?
Large
Small
Fill in the blank
=IF(__________, value_if_true, value_if_false)
lookup_value
criteria
range
logical_test
Which of these Vlookup functions is searching for an exact match?
=VLOOKUP (True, A2:B6, 2, C16)
= VLOOKUP (C16, A2:B6,3, TRUE)
=VLOOKUP (C16, A2:B6, 3, FALSE)
=VLOOKUP (C16, A2:B6,1, $C$4:$D$15)
The first argument in a VLOOKUP function tells the function __________
what to look for
where to look
to use an approximate or exact match
the column index number
VLOOKUP formula to be used to get result in the highlighted cell
=VLOOKUP(A2:B9,2,FALSE)
=VLOOKUP(D3,A2:B9,FALSE)
=VLOOKUP(D3,A2,2,FALSE)
=VLOOKUP(D3,A2:B9,2,FALSE)
Which of the formula below contain the correct syntax (formula arguments) for the VLOOKUP function?
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
=VLOOKUP(table_array, lookup_value, col_index_num, range_lookup)
=VLOOKUP(lookup_value, table_array, col_index_num, value)
=VLOOKUP(lookup_value, table_array, range_lookup)
Which of the following is NOT possible with VLOOKUP?
You can lookup values located in a different worksheet.
You can lookup values located in a column to the right of the column that contains the lookup value.
You can lookup values located in a column to the left of the column that contains the lookup value.
None of the above
Study the screenshot above. To find the price for the selected product (coffee in the example above), what formula would you type into the highlighted cell (B19)?
=VLOOKUP(A19,A2:C16,3,FALSE)
=VLOOKUP(A19,A2:C16,1,TRUE)
=VLOOKUP(A19,A2:C16,C,FALSE)
=VLOOKUP(B19,A2:C16,3,FALSE)
