Economist Writing Every Day

I teach macroeconomics, finance, and other things.

Often, I use Excel to complete repetitive calculations for my students. The version that I show them is different from the version that I use. They see a lot more mathematical steps displayed in different cells, usually with a label describing what it is. But when I create an answer calculator or work on my own, I usually try to be as concise as possible, squeezing what I can into a single cell or many fewer cells. That’s what brings me to to the sumproduct excel function that I recently learned. It’s super useful I’ll illustrate it with two examples.

Example 1) NGDP

One way to calculate NGDP is to sum all of the expenditures on the different products during a time period. The expenditures on a good is simply the price of the good times the quantity that was purchased during the time period. The below image illustrates an example with the values on the left, and the equations that I used on the right. That’s the student version. There is an equation for each good which calculates the total expenditure on the individual goods. Then, there is a final equation which sums the spending to get total expenditures, or NGDP.

Granted, autofill makes creating the third column relatively fast and easy. And, it’s good for explaining content to students. But what would *I* do? I’d forgo that third column entirely, thanks to sumproduct. Below is the same calculation more concisely. I dispense with the expenditures on individual goods and just skip to the part that I care about: the sum of the products. Of course, I can imagine a case in which I might want to know the spending on individual goods. But, it’s nice to skip it when I don’t care.

Example 2) Expected Return

What if you have a portfolio of assets? Or a portfolio of debt? Or a menu of possible outcomes, each with a different probability? Below is the expected performance of an asset and the probability that each outcome occurs. If we want to find the expected return, then we’d calculate the sum of weighted returns. Again, we can save ourselves the third column of work by using the sumproduct function.

Example 3) Division

‘Big whoop’ you might say. The function appears to save only one step and to have limited applications. Nay, I say! the sumproduct function has flexibility. Namely, you can transform the values in an array prior to the multiplication. Below is the student-version of calculating the expected return variance. The variance is the sum of the weighted squared deviations from the mean. What a mouthful! Not only that, what a spreadsheet-ful! The simple operator functions use four columns of calculation, plus a cell for the expected value and a cell for the variance. Of course, one could combine the smaller steps in order to reduce the calculated columns from four to two. But there’d still be ten cells of formulas.

Below is the truncated method of calculating the expected variance that uses the sumproduct function. First, it uses only one cell of of formula. That’s a 90% reduction from the simplified version of the example above. Specifically, I want you to notice how the values in the arrays can be manipulated. First, I nested the sumproducts so that I could calculate the average return like I did in example 2). I subtract that average from each possible return in column C, then square that difference. Only after these manipulations does the sumproduct function multiply the values of the arrays and then sum them. How about them apples!

At first blush, the sumproduct function in excel saves a negligible amount of time and appears to be a uni-tasker. But, thanks to some developer foresight and convenient syntax, we can complete a great number of operations all in one cell. It might take some time to get used to it. After all, an advantage of spreadsheets is that we can break problems down into their components across different cells. That makes it easier for us to understand the various parts and to debug. But, once you become very familiar with a process and you don’t need to decompose it every time, putting everything in a single cell improves efficiency and let’s you spend your time on your other priorities. And for that, cheers to the sumproduct function!