NEW
Font size
WorksheetsIGCSE EXCEL Formulas & Functions
Total questions: 20
Worksheet time: 14mins
Which of the formula's would output the price of the CHEAPEST item in the bakery?
=MIN(B2:B7)
=LOW(B2:B7)
=SML(B2:B7)
=BOTTOM(B2:B7)
Which of the formula's would output the price of the MOST EXPENSIVE item in the bakery?
=MAX(B2:B7)
=HIGH(B2:B7)
=LRG(B2:B7)
=TOP(B2:B7)
WHICH FORMULA CAN FIND OUT HOW MANY NUMBERS WE HAVE IN THIS TABLE?
=COUNT(A1:A10)
=COUNTIF(A1:A10)
=COUNT(B2:B7)
=AVERAGE(A1;A8)
HOW TO COUNT HOW MANY 78 IN THIS TABLE?
=COUNTIF(2:6,78)
=COUNTIF(2:7,78)
=COUNTIF(A2:A6,78)
=COUNTIF(A2:A7,78)
What does the Countif formula return?
Counts the number of cells within a range that meets the given condition.
Counts the number of words within a range that meets the given condition.
Counts the number of columns within a range that meets the given condition.
Counts the number of letters within a range that meets the given condition.
What does the Counta formula return?
Counts the number of cells within a range that meets the given condition.
Counts the number of cells in a range that are not empty.
Counts the number of columns within a range that meets the given condition.
Counts the number of columns in a range that are not empty.
What does the Sumif formula return?
adds the cells specified by a given condition or criteria.
adds up all numbers (arguments) in a row you specify.
allows you to pick individual numbers you want to add up by manually click on each number.
Counts the number of items in a set of values that meet your criteria.
Identify the formula for
VLOOKUP
=VLOOKUP(table_array, lookup_value, col_index_num, [range_lookup])
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=VLOOKUP(table_array, col_index_num, lookup_value,[range_lookup])
=VLOOKUP(lookup_value, table_array, [range_lookup], col_index_num)
What is the correct formula for this?
=HLOOKUP(A4, A1:F1,2, FALSE)
=VLOOKUP(B4, A1:F1,2, TRUE)
=HLOOKUP(A4, A1:F1,2, TRUE)
=VLOOKUP(A4, A1:F2,2, FALSE)
What formula is used to find East_01?
=HLOOKUP(A11, B1:B9,2, TRUE)
=VLOOKUP(A11, A1:B9,2, TRUE)
=HLOOKUP(A11, A1:B9,2, FALSE)
=VLOOKUP(A11, A1:B9,2, FALSE)
Jill wants to search her spreadsheet for a vaule in the first column of her tabel. What function should she use?
VLOOKUP
HLOOKUP
LOOKUP
SEARCH
What function is used to find price of Product_2?
HlookUp
Index
Match
VlookUp
What is the correct formula?
=VlookUp(A8, A1:B5, 2, FALSE)
= HlookUp(A8, A1:B5, 2, FALSE)
= VlookUp(A8, A1:B5, 2, TRUE)
Absolute cell reference
is group of selected cells which are highlighted
is reference which is automatically fills other cells
does not change when the formula is copied or moved to a new location
There are two types of references:
1._____________ : (B2:B6), C4
2.Absolute : $A$2, ($B$2:$B$6),
Changing
Fluctuate
Relative
Linked
If the mark is more than 50, the student passes. Which function we should use in D3? (We will use AutoFill for others)
=IF($B$3>50,"YES","NO")
=IF($C$3>50,"YES","NO")
=IF(C3>50,"YES","NO")
=IF(B3>50,YES,NO)
Which one of these functions gives the result shown in the picture?
=COUNTIF(D5:D13,"<>10")
=COUNTIF(D5:D13,"<15")
=COUNTIF(D5:D13,"=16")
=COUNTIF(D5:D13,">=10")
=COUNTIF(D5:D13,">=15")
