WorksheetsModelling Functions and Data Tables
Total questions: 8
Worksheet time: 4mins
Which of the options below best describes how SUMPRODUCT works?
SUMPRODUCT finds the product of multiple arrays and then sums up the products.
SUMPRODUCT calculates the product of elements within an array and then sums up those products.
SUMPRODUCT sums up the elements of multiple arrays and then calculates the product of the sums.
A colleague is unsure what order the arrays need to be entered in the SUMPRODUCT function. What is the best response to this?
It is important to consider the order of the arrays. If the arrays are not in the right order, an erroneous answer can be produced.
It is actually not necessary to use a particular order when placing the arguments in SUMPRODUCT.
How can SUMPRODUCT be used to add up values that meet certain criteria (perform a COUNTIF)?
It can’t fully be understood how Excel does this as this is odd for the SUMPRODUCT function to do.
This only works sometimes, it could produce an erroneous answer on a large dataset.
It finds the product element wise of one times whether each element in the array met the condition (1 if it did and 0 if it didn’t) and then sums up the resulting products.
For the Excel worksheet below, typing either of the following two formulas in cell G5 will return the same answer:
=SUMPRODUCT(B5:F5,$B$3:$F$3)
=SUMPRODUCT($B$3:$F$3,B5:F5)
True
False
The function of the Data Table tool can be summarized as follows:
Data Tables are a quick way of displaying how the outcomes of a calculation change by varying the inputs.
True
False
For the Data Table in A15:B22, when selecting the Data Table option, what is cell E16 referred to?
The column input cell.
The row input cell.
Where is the Data Tables tool located?
Go to the Home tab, locate the Data Tools group and go to the What-If Analysis.
You can access Data Tables through the right-click context menu.
Go to the Data tab, locate the Forecast group and go to the What-If Analysis.
For the Data Table in A26:G34, after clicking on Data Table, what do cells E16 and E18 represent?
E16 is the Row input cell; E18 is the Column input cell.
E16 is the Column input cell; E18 is the Row input cell.
E16 is the Row input cell; E18 is the Row input cell
