Excel exercises
Excel exercises: SUMPRODUCT and friends
Some totals need two columns multiplied before they are added, and some need to ignore rows a filter has hidden.
SUMPRODUCT multiplies arrays element by element and adds the results, which replaces a helper column. SUBTOTAL takes a function number first, so 9 adds and 1 averages, and it recalculates against the rows a filter leaves visible. AGGREGATE adds an options number that can ignore errors, hidden rows, or both. Choose the wrong option and the total is wrong.
Four exercises at level 3, and every answer is graded on the number the formula returns.
The two argument counts are what people mix up. A function number is a choice from a fixed list, an options number is a set of switches, and neither is a cell address. Write the pair down before building the formula. The group has four exercises, and in each a wrong option number changes the total.
A stale exercise is one you finished two weeks ago or longer. One that needs practice took a hint, or more than one try.
0 of 4 complete
- SUMPRODUCT: multiply the pair, then add them up Paid Value a table and take a weighted average from the same two columns.
- SUBTOTAL: one function number, six answers Paid Read the function number first, so one range can be added or searched for its largest value.
- AGGREGATE: a total that says what to ignore Paid Write a three-argument aggregate and know which cells the options number leaves out.
- Totals that multiply, skip and reconcile Paid Reconcile two routes to the same total and read the ratio as a pass or a fail.