Friday Challenge – Step Chart for an Excel Time Series

I was recently asked for assistance with this request:

Hi Steve,

I am trying to make a hypnogram with data that we collected over the course of a day. I want the y-axis to denote wake (W), other (O), and sleep (s) with just three tick marks. The y-axis should represent the time spent in these states. 

A hypnogram plots sleep patterns over time and is used to asses a person’s wake/sleep behavior at night (or during the day). It is used for diagnostic and treatment purposes for individuals with sleep disorders.

Some sample data: 

W8:20:49AM8:21:04
O8:21:05AM8:21:24
W8:21:25AM8:21:54
S8:21:55AM8:23:43
W8:23:44AM8:23:57
O8:23:58AM8:24:44
W8:24:45AM8:28:11
S8:28:12AM8:29:34
W8:29:35AM8:30:31
S8:30:32AM8:35:26

The second set of numbers is when the state ended.

Thank you,

Anna

 

You can learn more about our Excel step chart solutions here:

https://www.exceldashboardtemplates.com/how-to-easily-create-a-step-chart-in-excel/

https://www.exceldashboardtemplates.com/how-to-easily-create-a-step-chart-in-excel-2/

https://www.exceldashboardtemplates.com/how-to-create-an-excel-step-chart-formula-using-the-small-function/

https://www.exceldashboardtemplates.com/understanding-how-to-make-a-step-chart-in-excel-using-index-and-match-functions/

 

 

Even though you can use the tutorials above to help with this Excel challenge, there is a trick to working with a Time Series in Excel.  So it might not be a easy as it may first appear.

Give it a shot and let me know if you were able to solve this challenge.  I was able to solve Anna’s request and she was able to automate it with the formulas you see in the tutorials above.  I might even get a mention in her publication if it is accepted 🙂 Way cool!

 

Steve=True

 

1 COMMENT

LEAVE A REPLY

Please enter your comment!
Please enter your name here