Hi I have my electricity usage data in the following format (written in csv style for simplicity)
12:00 - 1:00, 1:00 - 2:00, 2:00 - 3:00.......... 23:00 - 0:00, date(dd/mm/yy), yyyy-mm, mm, weekend/weekday, day of week.
As such each day takes a single row and has 24 usage data points.
Here in Australia we have time of use metering which is further complicated by different retailers have their own time periods for when is peak, shoulder and offpeak rates and can differ at different times of the year. For example one retailer may have peak periods 2PM - 8PM Monday to Friday all year round where as another retailer has peak periods of 2PM to 7PM Monday to Friday during December, January and February and 5PM - 9PM during May, June, July and August and then no peak rates for the rest of the year. They do however on weekends where rates are peak during the week in those hours it is classed as shoulder rates.
As such I had the plan to have two other tables / worksheets which get referenced
Worksheet 1 - List each retailer I want to compare and the various costings
ie. ProviderName, Peak Rate, Shoulder Rate, Offpeak Rate
Worksheet 2 - List of Providers I want to compare and list the hours by month and put here when it is peak, shoulder, offpeak etc. (somethings similar to attached image
In the past data like this I have often gone with a complex VBA script that I make to resolve it but in learning more about SUMIFS, INDEX, MATCH, Crosstab and a bunch of other inbuilt functions I am thinking there is a far better way to do this by utilising inbuilt functions instead of reinventing the wheel as I always tend to do.
The final output I am thinking would be best in PivotTable / PivotChart format as I could choose what retailers I wanted to see and could see breakdowns by Qtr, Month etc. but only way I would currently think to do that is to in essence transpose the data into another sheet first (so that each day had 24 rows of data) which I would do via script (as I will want to do this on about a yearly basis).
Any pointers on the logical and easier way to do this would be appreciated. As stated quite proficient in VBA so if this is the best way let me know and if there is a particular way/ function call so I don't make it more complicated then this needs to be.
"::
12:00 - 1:00, 1:00 - 2:00, 2:00 - 3:00.......... 23:00 - 0:00, date(dd/mm/yy), yyyy-mm, mm, weekend/weekday, day of week.
As such each day takes a single row and has 24 usage data points.
Here in Australia we have time of use metering which is further complicated by different retailers have their own time periods for when is peak, shoulder and offpeak rates and can differ at different times of the year. For example one retailer may have peak periods 2PM - 8PM Monday to Friday all year round where as another retailer has peak periods of 2PM to 7PM Monday to Friday during December, January and February and 5PM - 9PM during May, June, July and August and then no peak rates for the rest of the year. They do however on weekends where rates are peak during the week in those hours it is classed as shoulder rates.
As such I had the plan to have two other tables / worksheets which get referenced
Worksheet 1 - List each retailer I want to compare and the various costings
ie. ProviderName, Peak Rate, Shoulder Rate, Offpeak Rate
Worksheet 2 - List of Providers I want to compare and list the hours by month and put here when it is peak, shoulder, offpeak etc. (somethings similar to attached image
In the past data like this I have often gone with a complex VBA script that I make to resolve it but in learning more about SUMIFS, INDEX, MATCH, Crosstab and a bunch of other inbuilt functions I am thinking there is a far better way to do this by utilising inbuilt functions instead of reinventing the wheel as I always tend to do.
The final output I am thinking would be best in PivotTable / PivotChart format as I could choose what retailers I wanted to see and could see breakdowns by Qtr, Month etc. but only way I would currently think to do that is to in essence transpose the data into another sheet first (so that each day had 24 rows of data) which I would do via script (as I will want to do this on about a yearly basis).
Any pointers on the logical and easier way to do this would be appreciated. As stated quite proficient in VBA so if this is the best way let me know and if there is a particular way/ function call so I don't make it more complicated then this needs to be.
"::