To make the job application process a bit more fun on the side, I am gathering some statistics on it. I would love to plot "number of applications submitted over time". I am limited to Google Sheets (doing this in matplotlib
would be a no brainer, and no, I do not have access to Excel).
Fortunately, Google Sheets proves a Timeline Chart type. Unfortunately, I cannot figure out how to use it to my end. As you can see in the screenshot of my spreadsheet below, I have a column N
where I enter the date at which I submitted the application.
I would now like to plot the number of cells in column N
which contain a date prior to date d
over date d
. My current approach involves column A
which calculates
=countif(N:N, "<="&N2)
for A2
("<="&N3
for A3
and so on). That is the number of rows with dates in column N
that are earlier than the one in the current row. So the first date evaluates to 0
, the latest date to the total number of cells with dates. Empty dates evaluate to 0
. If I plot N1:N1001,A1:A1001
in the Timeline Chart, I am left with this broken mess.
The problem seems to be the order of cells. Is there a way to tell the timeline chart to sort the data by date before plotting? Or is there a simple way to input sorted data into the plot without manually needing to sort it (I want to keep the original order/sort it by different criteria without destroying the plot). I am fine with a solution that duplicates the data automatically in an extra sheet or something. But no manual intervention must be needed.