Alternative way to chart data

[joemontenaro]

Can I recreate Chart 1 in the attached image (generated as a stacked area chart from the Format 1 data), from the Format 2 data?

[MrShorty]

It maybe depends on exactly what you mean by "create the chart from the format 2 data". I do not see any way to create the chart directly from the format 2 table. I could easily see using formulas based on the format 2 table to create the format 1 table, then the chart is built on the format 1 table. If I don't like seeing the format 1 table, I can hide that table.

Does using formulas to build the format 1 fit within your goal of building a chart from the format 2 data?

[joemontenaro]

It does and I could maybe work through generating those formulas on my own, but I was curious to see if there was any way to do it directly from my source data.

If you've got any pointers on the formulas, I'd appreciate hearing them!

Thanks!

It maybe depends on exactly what you mean by "create the chart from the format 2 data". I do not see any way to create the chart directly from the format 2 table. I could easily see using formulas based on the format 2 table to create the format 1 table, then the chart is built on the format 1 table. If I don't like seeing the format 1 table, I can hide that table.

Does using formulas to build the format 1 fit within your goal of building a chart from the format 2 data?

[MrShorty]

The formulas should be fairly simple IF() functions. The basic logic is If the time in row 2 is between the start and end times in columns L and M, then return the staff value from column K, else return 0. Assuming the upper left of your screenshot is A1, this formula might look like =IF(AND($L3<=B$2,B$2<=$M3),$K3,0). Note the mix of relative and absolute references. Then copy/paste/fill into the rest of the format 1 table.

I note that your screenshot shows the times left aligned in the cells -- which is default behavior for text strings. Numbers/times/dates are usually right aligned by default. If your times are really text and not true date/time serial numbers, then you will need to add a "convert time stored as text to real time" step.