Energy Cost formula

**aarbgick** · 12-23-2012, 08:40 AM

I was trying to find a way to make a formula to show the amount of money my hotel spends on wasted energy to vaccant rooms but I'm having a hard time figuring out just how to do it.

In one year there is 8,760 hours (24*365)
On any given day our property has 7 out of 10 rooms have lights left on and the average power usage is 92 watts per room.
The cost of electricity in the city of Memphis is 9.65 cents per kilowatt/hour.
We have 600 rooms total.
A few examples of what I've done by hand would be:
(1% vaccancy) 6*.7 = 4.2 rooms. 4.2*92w per room = 386.4 watts per room per hour. 386.4 watts * 8760 hours = 3384864 watts per hour. 3384864w/h /1000 = 3384.86 Killowatts = $326.64
(20% vaccancy) 120*.70 = 84 rooms. 84*92w = 7728 watts. 7728 watts * 8760 hours = 67697280w/h. 67697280w/h / 1000 = 67697.28 = $6,532.79

To get the cost I am using the calculator on http://www.handymath.com/cgi-bin/ele...i?submit=Entry
9.65 going into the cents/kw-hr, watts per room per hour going into the watts, and 8760 hours into the hours portion.

If anyone knows how to make a formula in excel to calculate 1-100% vaccancy that would be awesome as I can't for the life of me figure out how to do it without having to do each percent by hand.

**dogberry** · 12-23-2012, 08:55 AM

Hi

Can you post a sample worksheet so we can see the layout etc dont post confidental information
it will also get you a much better response.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

Chris

**aarbgick** · 12-23-2012, 08:59 AM

I don't currently have anything. I've just been using a calculator and paper since I couldn't figure out a way to formulate a formula. Just decided it is taking far to long that way and won't look very professional if I enter it all in a word document.

**dogberry** · 12-23-2012, 09:02 AM

Hi

OK when you get a chance do a sample file for 3 or 4 rooms so that we can see the layout and style that you want from that sheet we can work on formulas that you need for the master sheet.

Chris

**aarbgick** · 12-23-2012, 09:05 AM

I'm fixing to get off work and will try once I get my sleep. :-)

**dogberry** · 12-23-2012, 04:01 PM

Hi

I have done a very basic sheet for you. Is this the type of thing you are after.

**shg** · 12-23-2012, 04:33 PM

Similarly,

Please Login or Register  to view this content.

**darth.dims** · 12-23-2012, 04:52 PM

Hi,

Hope this is what you were looking for. The values you've listed are listed in separate cells with the formula right below. If you evaluate it you'll see it give you the same numbers in almost the same sequence as your original post (which should hopefully shed more light to sequence in the math).

If you change any of the values, the cost will also change.

best regards, dims

Power calculation.xlsx