Hydro consumption formula quandry

[Hutchcove]

Hi guys,

I'm looking to calculate my Hydro consumption on a particular device in my basement. I'm currently running UPS systems to test battery charge & other. I need to run each UPS for a total of 24 hours (charge + plus troubleshooting time). With that said, I'm looking at creating a formula that would sum the total cost of that/those devices depending on the amount I charge on any given day so that I may expense it back to the company asking me to do this.

Specifics below:

1 UPS is 800W consomption with 24 hour run time . kWh calculation is ((Watts)x(Hours))/1000)
So for the above UPS at 800W = 19.2 kWh

Now, the issue lies with the Cost / kWh. For the first 30 kWh - the cost of electricity is $0.0557 but after 30 kWh it increases to $0.0826.

if I only do 1 UPS on a day - the calculation is obvious but my question lies on what happens if I have 2 or more. Once I go over the 30 kWh (which I would on 2 or more) how to I manipulate the formula to give me the total of the first 30 kWh & then the additional cost above 30 kWh.

I'm assuming its relatively easy, but the format in which to setup the formula is escaping me (most likely will require an IF command)

Any assistance would be greatly appreciated.

 

[Smitty]

This probably isn't the most elegant solution, but seems to work:

Excel 2012
	A	B	C	D	E
1	Run time	Comsumption	Units	KWH	Cost
2	24	800	2	38.4	$2.3648
3
4	KWH	Cost
5	30	$0.0557
6	31	$0.0826
Sheet3

Cell Formulas
Range	Formula
D2	=C2*((B2*A2)/1000)
E2	=MIN(D2,30)*LOOKUP(30,A5:B6)+IF(D2>30,(D2-30)*LOOKUP(31,A5:B6),0)

HTH,