Excel: Graph square wave using time ranges for y=1, and a lack of them for y=0

Sorry for the bad question name.

I have a set of data in a CSV that contains time ranges (nanoseconds since epoch) like so

StartTimestamp,EndTimestamp
284473439207159,284473441207159
284473458747908,284473460285908
284473480805406,284473481909406
284473502295963,284473504295963
284473524525589,284473526079589
284473546781808,284473547889808

These represent the time a signal was on.

I'd like to create a graph that is a square wave, where X is time (from the first start to the last end) and f(X)={1 if X is in one of the ranges in the data set, 0 if it is not)

Example: Given the set

0,1
3,5
9,10

I'd like to see this

enter image description here

Any suggestions? Without generating the 0 values, I cannot figure this one out.

Solution 1:

First of all, you have to break the start and end numbers into separate values. You can do that with the Text to Columns function, or you can do it with formulas.

Here’s how to do it with formulas. I’ll assume that your comma-separated values are in Column A, starting in Cell A2. (And I’ll assume that you’ve already figured out that it helps to format those values as Text, to prevent Excel from thinking that each entry is a 30-digit number.)

In Cell B2, enter =LEFT(A2, FIND(",", A2)-1)+0
In Cell C2, enter =RIGHT(A2, LEN(A2)-FIND(",", A2))+0

(The +0 at the end converts the Text value returned by LEFT() and RIGHT() back to a number.)

And I guess you’ve figured out that the way to create a square wave chart is to have two data points for every X transition value. We can create those with formulas. You need a working area — for example, Columns AB:AC. (You can put it elsewhere if you want.)

In Cell AB2, enter =INDEX(B:C, (ROW()+3)/4+1, MOD(INT((ROW()+3)/2),2)+1)
In Cell AC2, enter =MOD(INT(ROW()/2), 2)

The AB formula fetches X values from Columns B:C, alternating between them. The AC formula generates zeros and ones to go with them. Select Cells AB2 and AC2 and drag/fill down. You now have data suitable for an X-Y Scatter Chart.

Your dummy/test data: