|
| |
• Up • AutoFilter • Count words • Sumproduct • Pivot Tables •
Excel Sumproduct formula
Undocumented uses
If you need to sum a number of ranges, or just a single range with a lot of
different criteria, then the best formula I have found is Sumproduct()
If you have just one criteria you can use SumIf()
If you have multiple criteria, you can use {Sum(If())} as an array formula,
but if you use Sumproduct() there is no need to press Ctrl-Shift-Enter, and
the syntax is simpler to work with.
Its primary purpose is to multiply corresponding components in the user
defined arrays, and return the sum of those products. It takes the format =SUMPRODUCT((array1)*(array2)*
...). It can contain up to 30 different arrays.
The following example shows a simple model to calculate the cost of four
different clinical procedures. Six main elements (or cost drivers) have been
identified: days spent on a ward; the time spent in operating theatre; the
hours of physiotherapy; the number of outpatient attendances required; the
cost of drugs and the cost of dressings. If the number of patient episodes
(in column C) are multiplied by the quantity of a cost driver (e.g. hours of
physiotherapy) you can calculate the total quantity of physiotherapy
resources that will be required i.e. (80x6) + (45x1) + (150x2). This lengthy
formula (in cell F10) can be replaced by =SUMPRODUCT(($C$6:$C$9)*(F6:F9)) .
[a]
Using a lot of selection criteria
For the data in the table above to become a useful costing model it needs
two further developments:
· the number of episodes must be multiplied by all of the various cost
drivers (i.e. the whole range D6:I9).
· the items such as Ward Days must be multiplied by their standard cost in
order to convert everything into financial values.
The following table contains the costs of each element (e.g. each day a
patient occupies a bed on a ward costs £87). Note that drugs and dressings
are already specific cost values and therefore have no common costs.
[b]
SumProduct will accept the whole range of cost drivers as a single array
(D6:I9) and will also allow you to enter another array of costs (D2:I2)
which is the same width but of a different depth (only 1 row).
[c]
The formula in D13 multiplies the numbers of episodes by the quantity of
resources and by the cost of each element. =SUMPRODUCT((C6:C9)*(D6:I9)*(D2:I2)).
The final row (15) shows the average cost of each type of procedure and is
simply calculated by dividing the total cost by the number of episodes.
SUM with conditions (Replace Sumif, and array Sum(If())...
An Excel array function can be used to add up or count a selected range of
values which meet a specified 'IF' criteria. It only allows a single
condition to be specified. SumProduct can perform calculations using any
number of different criteria (within the limit of 30 different arrays).
In the example above, cell E14 calculates the number of episodes meeting a
condition that the Type of procedure (in cells B6:B9) must be an
'Emergency'. The formula is =SUMPRODUCT((C6:C9)*($B$6:$B$9="Emergency")).
The value above it in cell E13 is similar to D13, only with an extra array
condition added.
Instead of each array argument being a range of cells to be multiplied, one
of the arrays has now become a condition (or criteria) specifying the items
to be SUMmed.
You can use adapt this formula to include numerous different rules e.g. =SUMPRODUCT((C6:C9)
* (F6:F9) * ($B$6:$B$9<>"Elective") * (F6:F9<5)). In this illustration
episodes are multiplied by hours of physiotherapy only where the type is not
elective and the number of hours of physiotherapy is less than 5. The answer
is 300.
Counting entries
All of the examples above contain the '*' operator to symbolise mulipication
of each pair of values. The function can return other mathematical values by
using /+ or - instead.
It can be adapted to perform a COUNT function by using a formula such as =SUMPRODUCT(($H$6:$H$9>450)*1).
This counts the number of procedures where the Drugs cost per patient
exceeds £450.
The function is ideally suited to calculating weighted averages.
Calculating a weighted average using Sumproduct
A common statistical technique to summarise a selection of values is the
arithmetic mean - generally known as the average. If measuring the average
price of foodstuffs you could take a list of products available and then
calculate the average. E.g. Bread (60) + Milk (30) + Beef (300) + Caviar
(5,000) = Average price (1,348). This is misleading however. The average
cost of an item in your shopping basket is unlikely to be 1,348 because it
would seem reasonable to adjust the values to reflect the relative
importance of each item. Milk is more important because it is typically
purchased more often than caviar. A retail price index would take this into
account and use a weighted average which gives more prominence to commonly
bought items.
Excel does not contain a built in function to calculate a weighted average.
It is however easy to do it using the SUMPRODUCT() function in a simple
formula.
SumProduct() multiplies two arrays (or ranges) together and returns the sum
of the product. In the illustration it would calculate '(B4 x C4) + (B5 x
C5) + (B6 x C6)'. The formula in cell B9 is: = SUMPRODUCT(B4:B6, C4:C6) /
SUM(C4:C6) The result shows that the weighted average is less than the plain
arithmetic mean. This is because it has taken into account the larger number
of staff being paid the lower salary.
The weighted average can also be used for assessing the risk or determining
the probability of various outcomes. If a judgement is made about the
likelihood of various weather conditions for an outdoor sporting and the
effect on ticket sales, a predicted value of sales can be calculated using a
similar formula as the previous example. =SUMPRODUCT(G16:G19, H16:H19)
returns the value of 7,380. The probability values (G16:G19) are already
expressed as percentages (totalling 100% or 1.0) and so there is no need to
divide by SUM(G16:G19).
| |
|
