1

I was given a spreadsheet with dates and timestamps of when one of our systems went down and came back up with the task of creating a visualization of the data. The Date and Timestamps were embedded in a string, A & B columns, and I was able to create columns that extract the Date and Timestamps, M & O columns, and then another column that finds the difference between those timestamps, so the total time elapsed, but I am having trouble getting the data to plot onto a bar chart.

Spreadsheet screenshot with my added column in red Spreadsheet screenshot with my added column in red

I used the formula =TEXT(O2-O3,"h:mm:ss") to find the time elapsed (TIMEOUT [N] column)

When I try to create a graph it gives me a chart with no data plotted/drawn a chart with no data plotted/drawn I have changed so many settings I can't remember them all. Though, I think it has everything to do with the data format of time confusing Excel. I just need Excel to put the date at the bottom and a bar above it showing a visualization of how much time elapsed in seconds from the outage to it coming back on. I am sure there is probably a simple solution, but it is eluding me at the moment. Any suggestions are appreciated.

Improve this question
7
  • What exactly are you trying to visualize? From the draft chart you show, it appears that you're looking for total outage by occurrence (which could result in multiple plots per day). You could also plot total outage by day/week/month etc... if you're simply looking to quantify the outages. If you're looking to analyze when they occur, you could also create a timeline to compare outage patterns over some periods (e.g. day-over-day or week-over-week). Any clarification you can provide will help focus the answers you get.
    – dav
    Commented Nov 1, 2021 at 20:31
  • @dav I'm trying to get the time out (10 secs, 20 secs, etc) to actually plot on the chart with the day it happened on the x-axis. If you look at the chart Excel creates right now, it has the dates on the bottom (good), but no data showing up as bars to visualize the amount of time out (bad). It won't put the numbers on the chart at all right now.
    – Jordan
    Commented Nov 2, 2021 at 12:32
  • Using your data example, on 10/21 you had two outages (22 sec and 11 sec), so do you want 10/21 displayed once with both values or twice-once with each value? If once with both values, do you want them separate (stacked 22 sec and 11 sec) or aggregated once (33 sec)?
    – dav
    Commented Nov 2, 2021 at 15:57
  • @dav they need to be two separate bars next to each other, so 10/21 displayed twice, one bar for 22 secs, the second bar for 11 secs
    – Jordan
    Commented Nov 2, 2021 at 16:11
  • Also, have you tried changing your vertical axis values? Excel stores date values as integers and h/m/s as decimals of those integers. So 1 sec = 1d/24h/60m/60s = 0.000011574 hours, or 22s = 0.00025463 hours. Since your vertical axis values are 0.1 days, your seconds value may be there, but so small that it's not visible. A quick check would be to change your vertical axis to log scale or change the major divisions to something very small like 0.0002 and a max of 0.001.
    – dav
    Commented Nov 2, 2021 at 16:14

1 Answer 1

Reset to default
0

Your problem appears to be that your vertical axis bounds are too large for your values. The outages in your sample run from 7 seconds to 23 seconds. In Excel terms that equates to 0.000081s to 0.000266s. Your chart's bounds appear to run from 0-1, with a major unit value of 0.1d which is equivalent to 2h:24m or 8640s. So, your max value of 23s is only approx 0.3% of your major unit. Here's a chart with the values of 7s, 23s, and 8640s shown:

Default Chart

If you change your vertical axis to logarithmic scale, you'll begin to see the difference:

Log Scale

So, based on your initial outage values, you could use a maximum bounds value of 0.001, with major units of 0.0001, and get a chart that looks something like:

Updated Scale

Although, in this case the upper scale is too small to properly show the scale of the 8640s column, so again, if you have huge differences in values you'll need to work out the presentation to accurately reflect both large and small.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .