How to use IF statements to calculate power usage and cost

[garygiles01]

Hello,

I am trying to use IF statements (I think this is correct) to work out power usage of a machine at certain times to therefore workout the cost associated with running this.

Please see the photos with the information. I am unsure of the formula to write to work out the amount of time the machine is running between certain times (peak power times, off-peak times, etc).

The overall goal is to input the start and end kw/hr reading as well as the start and finish time of the machine. Excel will then do the rest calculating the time in each period (peak, off-peak, etc) and therefore a total cost for that day.

Thanks to @Hans Vogelaar for responding to a previous post (forgot my login details and therefore have a new Microsoft Account) which had an answer, but I could not translate that work into what I am trying to achieve above. Sorry I didn't get a chance to say thank you!

Hopefully someone can help me out.

Thanks in advance.

[#2]

@garygiles01 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#3]

Thanks @Hans Vogelaar, very much appreciated.

Here is the link to the sheet. Sheet 1 was your first iteration.

Thank you again in advance.

https://1drv.ms/x/s!Am09L0ilmxefb7GNEPbPXOtkSDc?e=0TW6pC

[#4 · Accepted solution]

@garygiles01 

See the attached version. The formulas are simpler since you want to calculate the periods for every individual day.

The cost now takes the change in rate into account.

[#5]

@Hans Vogelaar Many thanks indeed!

This is sincerely appreciated and works well. I just had to manipulate the 'cost' column so it calculated the rates as a percentage of the Kw/hrs used.  

Only posting the revised link if it's of interest. Otherwise thanks again.

https://1drv.ms/x/s!Am09L0ilmxefc13Z4Dlxpo0OZSI?e=YaR4sb

G