2

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.

  • Example: Open - Close
  • first record: 2/10/2013 - 3/1/2013
  • second record: 2/15/2013 - 3/5/2013

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.

Improve this question

1 Answer 1

Reset to default
2

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,"")

Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

you shouldn't skip days if you are presenting snapshots for particular days instead of aggregates for particular intervals
@deathApril The category labels I elected to use in my example were for ease of exposition, nothing more. This formula would work with any interval of category labels, 1 day, 7 days, 14 days, whatever.
@deathApril FYI I did update my screenshot per your suggestion. Cheers. :)
Ok, that helps, particularly with a histogram. I am concerned though, because the full dataset spans several years, so the sheet will be unwieldy and the graph will take some formatting. It should work though. If I understand correctly, countifs is implicitly combines the ifs by "AND". is there a way to get an "OR" clause? i.e.: countsif data is (A or B) and above start date and below finish date? The OR function won't work here because it won't accept arrays.
If this solves your immediate problem, consider "accepting" the answer or at the very least upvoting it. Wouldn't an "OR" operator double-count some entries? I guess I don't understand why you might prefer an "OR" operator in this case.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.