I am attempting to calculate the value between two points. I have an electric usage baseline and I want to setup a spreadsheet to calculate the values for the tier one and tier two rates. I have the values for each tier and I would like to be able to take the baseline, extrapolate the value that belongs in the tier one range and use the amount that is higher than tier one to extrapolate the value for tier two and so on. Can someone help me to figure out how to do this?
I'm willing to give it a shot, but can you upload a test file that shows exactly what you are looking for?
Solar.xls
Attached is the XLS file. If you look on the September page you will find the section that has the "PG&E Rates" and below the "Rate Charges" We are attempting to calculate the charges per tier. As you can see T1, T2 etc have percentages attached to them along with usage amounts above the baseline.
Let me know if you need anything else and thank you very much for your help.
I'm having a little trouble understanding everything you need from the spreadsheet, but please look at the attached version of your file and see if the section I added to the right of your data is anything like you were looking for. Change the Consumed amount in O7 to see it in action.
Solar2.xls
If you look at line 8 there is a total amount of electricity used. What we need is to have anything over the baseline, located in cell D6 but less than E6 placed into D or E 8 and then the same for the T3 and T4 info as the amount progresses.
Royzer,
I did look at your addition; however, we are looking for the amount that would be between each tier to be placed in that section. For instance baseline would be 1083, tier one would be 325, tier two would be 596.66 based on the total of 2044.66 this would show the amount of usage per tier. Creating the dollar calculation from each of these number would be easy from there.
I'm working on it. I think I am close, but it will probably be tomorrow before I can show you something.
Okay, let's see if I'm on the right track. When the "Total Consumed" number in C16 changes, the fields I've highlighted in yellow in the "PG&E RATES" tier box should show the correct amount by tier. In the "RATE CHARGES" box I tied the amounts for T2 - T5 to the rates you show in red in column J. I used the same tier rate on T5 as on T4, however.
Let me know what you think.
BTW the total consumed number in the box is a few cents shorter than the example you gave. I cleared it while I was working on it and I couldn't remember what it originally was.
Solar102412.xls
Last edited by Royzer; 10-24-2012 at 08:11 PM. Reason: add BTW
Royzer,
Everything is correct with the exception of B8 it should show the amount from 0-1083 and the rest are correct.
Give this one a try:
Solar110712.xls
Perfect thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks