Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating a Utility Cost calculator
I am trying to calculate the cost of electricity for a given customer by
entering the zip code they live in and the amount of electricty used. This requires using information from the Utilities website, a zip code baseline Territory chart and a Territory Rate chart. I have no problem downloading each chart and storing locally if this makes it easier. Information is organized so that all zip codes are in row A and each teritory code in corisponding row B The Territory Rate Chart has the Territory Code in row A then row B has the coresponding Baseline quanity for summer and row C has the value for winter. What kind of if statement do I need to write so that when a zip code is entered and a month by month electricty usage is given it returns baseline values so that i can compute cost of electricty? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating a Utility Cost calculator
Set your sheet up with your monthly schedule on Sheet 1, zip code/region code
table on Sheet 2, and region code/summer & winter rates table on Sheet 3. I am assuming that both tables have a title row. Sheet 1 zip code entry cell is B1 Enter the following formula in Cell B3: =VLOOKUP(Sheet1!B1,Sheet2!A2:B50,2) Adjust the A2:B50 range in the formula to match the range of your zip code table. Set up month labels in Column A starting with January starting in row 6. Enter monthly usage in Column B corresponding to the labels. Enter the following formula in Column C for winter months: =VLOOKUP($B$3,Sheet3!A2:C50,3) Enter the following formula in Column C for summer months: =VLOOKUP($B$3,Sheet3!A2:C50,2) Adjust the A2:C50 range to match the range of your region/rates table. You can then multiply the two columns to calculate monthly cost. Hope this helps... "Eric" wrote: I am trying to calculate the cost of electricity for a given customer by entering the zip code they live in and the amount of electricty used. This requires using information from the Utilities website, a zip code baseline Territory chart and a Territory Rate chart. I have no problem downloading each chart and storing locally if this makes it easier. Information is organized so that all zip codes are in row A and each teritory code in corisponding row B The Territory Rate Chart has the Territory Code in row A then row B has the coresponding Baseline quanity for summer and row C has the value for winter. What kind of if statement do I need to write so that when a zip code is entered and a month by month electricty usage is given it returns baseline values so that i can compute cost of electricty? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro | Excel Discussion (Misc queries) | |||
Mileage Claim Formula | New Users to Excel | |||
Creating lookup formulas for material cost spreadsheet | Excel Worksheet Functions | |||
Help creating a cost range | Excel Discussion (Misc queries) | |||
Indexing a row | Excel Worksheet Functions |