I have a machine that runs on an irregular schedule, and records the power usage into an Excel file. The machine automatically turns on at a different time every day and is sometimes off for a few days. For example:
20/3/2017 13:00:00 0kW
20/3/2017 14:00:00 13.1kW
20/3/2017 15:00:00 12.9kW
20/3/2017 16:00:00 0kW
...
21/3/2017 13:00:00 0kW
21/3/2017 14:00:00 0kW
21/3/2017 15:00:00 12.5kW
Notice that a number of the entries show a usage value of zero. I want to plot a graph of the data showing only the periods when the values are non-zero, and have no gap for the excluded values.
What the graph looks like with the table I have:
What I actually want – gap for zero is totally removed:
How can I do that?
Best Answer
You need to remove
0
values from your data to let Excel understand you don't want to plot it.Easiest way for that is to use a calculated column:
=IF(D2=0,"-",D2)
Theoretically, now you can set your chart now, and it'll exclude those points, however I couldn't set it up with a normal charts.
But it works with a pivot chart:
date time
non-zero
; set "summarize field by" to averageUpdate
If you want to exclude times with
0
also from X axis range, then just filter them out in pivot table (move "non-zero" column to filters)Or, in this case you don't even need a calculated column, you can plot and filter directly your "values" column.