Cheers to Sumproduct!

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.

Continue reading

Everyone’s an Expert: Easy Data Maps in Excel

I love data, I love maps, and I love data visualizations.

While we tend not to remember entire data sets, we often remember some patterns related to rank. Speaking for myself anyway, I usually remember a handful of values that are pertinent to me. If I have a list of data by state, then I might take special note of the relative ranking of Florida (where I live), the populous states, Kentucky (where my parents’ families live), and Virginia (where my wife’s family lives). I might also take special note of the top rank and the bottom rank. See the below table of liquor taxes by State. You can easily find any state that you care about because the states are listed alphabetically.

A ranking is useful. It helps the reader to organize the data in their mind. But rankings are ordinal. It’s cool that Florida has a lower liquor tax than Virginia and Kentucky, but I really care about the actual tax rates. Is the difference big or small? Like, should I be buying my liquor in one of the other states in the southeast instead of Florida? Without knowing the tax rates, I can’t make the economic calculation of whether the extra stop in Georgia is worth the time and hassle. So, the most useful small data sets will have both the ranking and the raw data. Maybe we’re more interested in the rankings, such as in the below table.

But, tables take time to consume. A reader might immediately take note of the bottom and top values. And given that the data is not in alphabetical order, they might be able to quickly pick out the state that they’re accustomed to seeing in print. But otherwise, it will be difficult to scan the list for particular values of interest.  

Continue reading