Forum Discussion
Formulae / approach advice please
Hi,
I wonder if you can provide some guidance?
Can excel choose a value of a cell so that some logic is true? Allow me to explain......
In my example, I want to compare the impact of an electricity tariff which has a peak and off-peak price, using excel to identify the amount of off-peak usage required to make the tariff cheaper than a single flat-rate tariff.
So
- take a consistent usage of 6 (kWh in this case).
- flat rate price per hour is 0.2966
- dual rate tariff peak = 0.075
- dual rate tariff off-peak = 0.3566
Can we use excel to work out how much off-peak usage per day is required to make the dual rate tariff cheaper?
Thanks!
Hi Beeeemster,
My first thought is, your off peak rate is higher than the flat rate.. So comparing the 2 tier system to the flat, no amount of off peak work will be less than flat rate!!
Assuming that was a typo and swapping the 2 tier rates. so off peak is .075 and peak is .3566I calculated 6 hours at flat rate for 1.7796.
Then I simply built a goal seek model where X = number of off peak hours.
I started with X = 1, then ran goal seek, setting the final total to the amount I calculated.
anything over 1.27 hours of off peak time and you are better off in the 2 tier system
x 1.278409 off on 1.278409 4.721591 Rates 0.075 0.3566 subtotal 0.095881 1.683719 Total 1.7796 Thanks TomHinkle
Thanks for the help.
My mistake - the off-peak rate should be 0.0075 i.e. 7.5 pence. I'm trying to compare having 2 time-based prices, with one flat-rate price. I'm trying to use Excel to see the number of off-peak (low-rate) hours required, in order to make the dual-rate tariff better value.
Tony
Hey,
I've been able to successfully use the Goal Seek function.
Thanks all for your help :O)
Beeeemster That would be:
=Kw*( peak_rate - flat_rate ) / ( peak_rate - offpeak_rate )
This will return the break-even point for off-peak usage. I.e. dual rate cost = flat rate cost
