How to plot Demand and Supply curves in Google Sheets?
Thursday, 9/24/2020
It turns out that plotting Demand and Supply curves on Google Sheets may not be as easy as it seems. This is because unlike MS Excel, Google spreadsheet requires a shared X-axis (common independent variable) for multiple data series. Mathematically, quantities demanded & supplied (dependent variables) are functions of the price (independent variable) and many other variables. Technically, this means that supply, as well as demand curves, should be drawn with the price on the horizontal x-axis, since it is the independent variable. However, due to the historic convention price - f(x)) - is put on the vertical, y-axis and quantity - x - is put on the horizontal, x-axis. And this makes plotting data on Google Sheets slightly complicated. Let's take a look at a specific example using some hypothetical data.
Here is the sample data we will be using to plot demand and supply curves. In the table below, column A provides information about market price, columns B and C are for quantities of demand and supply respectively.
Generally speaking, Google's spreadsheet is ideal for one-click plots. Just select the data range, then go to Insert --> Chart. Google's AI will correctly predict that the chart we are looking for is a line chart and will produce the following Plot #1. In Plot #1, the quantity is on the y-axis and the price is on the x-axis. There is nothing wrong with this plot, except our demand & supply graph is different than the traditional textbook graph.
If we open the Chart editor for Plot #1 (by double-clicking on the plot), we will have the following chart information.
As you can see from the chart properties, Sheets is using column A (price data) as labels i.e. the price is presented on the x-axis as an independent variable. Now to convert this graph to conventional demand & supply graph we have to manipulate original data and prepare the following table.
Here we created a table that lists all quantity data from the original table and maps it to corresponding demand and supply prices. The first column of the table represents Quantity (dependent variable), the next two are Demand & Supply prices, respectively.
To create the above table, enter the following formula in cell E2:
=Sort(Unique({B2:B;C2:C}))
The formula above will extract unique quantity values from demand and supply columns of the original table and will present them in ascending order.
The next task is to extract demand prices from the original table and map them with quantities on column E. Enter the following formula in cell F2:
=IfError(Index(A:C,Match(E2,B:B,0),1),"")
Similarly, we will extract supply prices and map them with quantities on column E. Enter the following formula in cell G2:
=IfError(Index(A:C,Match(E2,C:C,0),1),"")
Now using our new table, we can create our "regular" demand & supply curves. Select the data range, then go to Insert --> Chart. It should generate a table similar to Plot #2.
Plot #2 should have the following properties (double-click on the plot to reveal it):
As can be seen, our demand curve has gaps. This is due to the "missing" data points in our new table. There is an easy fix to it using Chart editor. Go to Chart editor --> Customize --> Chart style and check: Plot null values.
Note: You can also put a checkmark on Smooth to smoothen demand and supply curves.
We will generate the following demand & supply graph. Here is the link to the original Google Sheet with data and graphs: https://bit.ly/2EyFV2c
