Tracing continuity in data

[Lekan17]

Dear member,
I have got this duty cycle chart that I want to be able to create an Excel sheet for so that I can use it for future projects. I have attached the Excel sheet for your reference. I need to be able to feel in the "Total amperes per period (Amperes)" column automatically using Excel functions/codes. The "Periods" are initiated whenever starting time of a Load intersect another Load(s) running time (i.e. Start time to End time) and it will run till the "End time" of the Load(s) with the closest "End time" as illustrated in the chart of "Figure A.2".
Thanks for your help in advance.
Regards,

[Glenn Kennedy]

In E19, copied down:

=SUMPRODUCT(($C$5:$C$10=INDEX($K$2:$N$7,MATCH(D19,$J$2:$J$7,0),))*$D$5:$D$10)

[Lekan17]

Hello Glen,
Will it be possible to recreate the Chart in Figure A.2 as per the attached file in Excel? I would be grateful if you are able to help with that too. Thanks

[Glenn Kennedy]

No Excel file, with or without a chart, is attached!!

[MrShorty]

As I explained yesterday (https://www.excelforum.com/excel-gen...-in-excel.html ), I would build it as a stacked area chart. Most of the work is building the data table for the chart. Here's the table I used (manually entered, since it only took a few minutes.). Select the table -> insert stacked area chart -> Select the horizontal axis -> format axis to be a "date" axis.

Please Login or Register  to view this content.

Automating the creation of the data table involves coming up with formulas to extract the start and end times from the source data (copied twice), then identify when each load is on and off and the times they switch from off to on/on to off.

[Lekan17]

Hello Glen,
It's the same Excel file you used to solve the first question.
Thanks

[Lekan17]

Thanks for help MrShorty. I would prefer it to be done automatically.
Regards,

[Glenn Kennedy]

Found it! It was out of sight at the far right. Just like Demonic Scummings.

I'm not great with Excel charts. I always preferred FigP for Windows... a powerful, yet tiny graphics package for the sciences.

[Lekan17]

Thanks so much for your help Glenn... You are a legend