CODEWITHSUNDEEP
excelchartstime-series
user2217917
user2217917

Reputation: 23

Excel time series data plot

I am trying to plot some time series data, but in a way that has stumped me so far. The salient part here is that each data point is associated with an open date and a closed date. I would like a time series line graph that counts the number open on a given date.

The graph I'm looking for would start at 0, rise to 1 on 2/10 rise again to 2 on 2/15 then drop down 1 on 3/1 and back to 0 on 3/5.

The actual dataset contains hundreds of records, so manual processing is out of the question. I'm sure there must be an easy way to do it, but I have not found it yet. Tried help and google search, but I'm not exactly sure what I'm looking for.

Upvotes: 2

Views: 3338

Answers (1)

David Zemens
David Zemens

Reputation: 53663

Use the CountIfs() function like so:

CountIfs for Date parameters

So, you specify the category labels, and then use the COUNTIFS() function to evaluate, for each category label, how many records are open at that time.

You can use the result of the Countifs function as the frequency for a histogram, time series, bar chart, etc.

Then, plot the data in columns E & F (or however your sheet happens to be arranged) to create the chart.

Edit

To include blank values in the count, modify the formula thusly:

=COUNTIFS($B$3:$B$7,"<="&E3,$C$3:$C$7,">="&E3)+COUNTIFS($B$3:$B$7,"<="&E3,$C$3:$C$7,"")

Upvotes: 2

Related Questions