Peak/ Off-Peak/ Shoulder calc

[marty31]

HI,

I have a mechanical schedule spreadsheet where i calculate all buildings energy consumption. I have to calculate Peak / Off Peak and Shoulder consumption. I am hoping someone can assist with the required easy calculation. I have bee given the sheet from a previous employee with calcs already in place for Peak and off peak.As there is numerous times for peak and shoulder this is beyond my limited maths capabilities. Thanks in advance


[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl1280, width: 64"]MONDAY[/TD]
[TD="class: xl1281, width: 64"] [/TD]
[TD="class: xl1281, width: 64"] [/TD]
[TD="class: xl1282, width: 64"] [/TD]
[TD="class: xl1276, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl1277, width: 64"]START[/TD]
[TD="class: xl1277, width: 64"]STOP[/TD]
[TD="class: xl1278, width: 64"]PEAK[/TD]
[TD="class: xl1278, width: 64"]OFF PEAK[/TD]
[TD="class: xl1278, width: 64"]SHOULDER[/TD]
[/TR]
[TR]
[TD="class: xl1275"]5[/TD]
[TD="class: xl1275"]20[/TD]
[TD="class: xl1279"]13[/TD]
[TD="class: xl1279"]2[/TD]
[TD="class: xl1279"] [/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Peak Calc[/TD]
[TD="colspan: 4"] =IF(OR(A3="",B3=""),"",IF(B3<=7,0,MIN(B3,23)-MAX(7,A3)))[/TD]
[/TR]
[TR]
[TD]Off Peak[/TD]
[TD="colspan: 4"] =IF(OR(A3="",B3=""),"",(B3-A3)-C3)[/TD]
[/TR]
</tbody>[/TABLE]

 

[bellman101]

Hi Marty,

May I ask, what is your actual question? What are you trying to achieve?

Cheers
JB

 

[marty31]

Hi Marty,

May I ask, what is your actual question? What are you trying to achieve?

Cheers
JB

Hi JB

I am trying to work out the calculation for all if possible. The sheet i was given is for Victoria which only has peak and off peak times (Off Peak - 11pm to 7am Mon to Fri and all times on weekends, Peak = All other times).
In NSW the times are different (Off Peak - 10pm to 7am, Shoulder - 7am to 2pm then 8pm to 10pm, Peak - 7am to 9am and 5pm to 8pm Mon to Fri).
Any assistance is appreciated

Cheers

 

[thisoldman]

The NSW times are confusing as given:

Off Peak - 10pm to 7am,
Shoulder - 7am to 2pm then 8pm to 10pm,
Peak - 7am to 9am and 5pm to 8pm

What rate should apply to 7AM–9AM, Shoulder or Peak?
What rate applies for 2PM–5PM?

I used this for the rate schedule:
[TABLE="class: grid"]
<tbody>[TR]
[TD]Offpeak1 Start[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Offpeak1 End[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Peak1 Start[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Peak1 End[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]Shoulder1 Start[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]Shoulder1 End[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD]Peak2 Start[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD]Peak2 End[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Shoulder2 Start[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Shoulder2 End[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD]Offpeak2 Start[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD]Offpeak2 End[/TD]
[TD="align: right"]24[/TD]
[/TR]
</tbody>[/TABLE]

Using that schedule, I came up with these formulas. I assumed START values are between 0 and 23, STOP values run from 1 to 24.

Peak rates occur during the hours between 7 and 9, and between 17 and 20. I find I have difficulty mentally parsing nested IFs; Boolean logic is easier for me. I converted the Excel AND expressions to Excel Boolean formulas, where TRUE equals 1 and FALSE equals 0. =AND(A3< 9,B3>7) is the same as =(A3< 9)*(B3>7).

Peak rate hours formula:

=IF(OR(A3="",B3=""),"",(A3< 9)*(B3>7)*(MIN(B3,9)-MAX(A3,7))
+(A3< 20)*(B3>17)*(MIN(B3,20)-MAX(A3,17)))


The OFF PEAK hours are easier—anything before 7AM or after 22(PM):

=IF(OR(A3="",B3=""),"",MAX(7-A3,0)+MAX(B3-22,0))


Determining the SHOULDER hours is very easy. It's the total hours minus the sum of the PEAK and the OFF PEAK hours:

=IF(OR(A3="",B3=""),"",(B3-A3)-SUM(C3:D3))


If the rate schedule I gave is correct, the formulas should work. I'll be happy to make any needed corrections.

 

[marty31]

Hi Thisoldman

Thank you so much for your help.
Greatly appreciated