How to graph frequency of boolean values over time in Excel?

Question

I have three columns of excel data I want to graph: name, a time value, and a Boolean (TRUE or FALSE). Essentially I want time on the X axis, and the counts or frequency of TRUEs and FALSEs on the Y axis.

Wat is the best way to do this, and which graph type would best represent this data?

I have around 900 data points.

Would a stacked area chart might be the best way to show the frequency of T v. F over time?

Answer 1

Your existing data is arranged in what is called a 'time-history' format, where you have discrete data points taken at specific times.

How you are wanting to display your data is called 'time-at-level'. This type of display is used to display the number of times that an event occurred that fell between a specific defined range for that event. Normally this would be used on a dataset that has a varying value over time. Your case is a little bit unique in that the varying values over time are actually 'time'.

So to answer one of your questions, yes a stacked area graph would be a decent way to show your results, or a grouped column chart would work as well.

In order to change your time-history data into time-at-level data you will need to crunch through your data, probably in a vba macro. If your time data points occurred at regular intervals, you could just break up the data into regular groups in Excel and used the COUNTIF function on each of the groups.

In your case you will need to loop through each line of data, determine if it is true or false, and increment a counter for the correct bin.

If you know the number of bins you want to break your time data into, the bin size can be calculated:

Binsize = (Largest time value - Smallest time value) / number of bins

Then you can set up an array or list that contains the maximum time value that can occur in each bin:

Bin 1: maxvalue = Binsize
Bin 2: maxvalue = Binsize * 2
.
.
Bin n: maxvalue = Binsize * n

Actually, you will need 2 arrays/lists. One for true values, one for false values.

At this point you can loop through each row of your data. You examine the time value and determine which bin it belongs in, then see if it corresponds to TRUE or FALSE, and increment the appropriate counter associated with that bin.

You will then have a count of all TRUEs and all FALSEs that occured in regularly spaced intervals, and you can redisplay your data in a time-at-level format and chart it however you want.

Answer 2

I did not get your question clearly but I think these points can solve your problem.

1. If you want to show % True and % False against a date then you can display it in Column chart. Where your X-Axis would be the date/time and a single column will have different colour to represent True and False %. All columns of your graph would be of same height but different % of True and False.

2. If you want to show only Ture % in graph and your date is not at regular interval then you have to make range for some date and calculate the True % for decided range. For example you have chosen range of one week and during that week you have 3 values 70%, 60% and 50% then avg True % would be (70+60+50)/3 = 60%. So in your graph you will have a point at 60% for considered week, lets say its third week in your range. So your point on graph would be (3,60).

Let me know if I got you wrong. and mark as answer if it helps you :)