Forum Discussion

Copper Contributor

May 14, 2022

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 ele...

excel

Formulas and Functions

TomHinkle

Copper Contributor

May 14, 2022

Beeeemster

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 .3566

I 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

Beeeemster

Copper Contributor

May 15, 2022

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

Beeeemster
Copper Contributor
May 15, 2022
Hey,

I've been able to successfully use the Goal Seek function.

Thanks all for your help :O)
- Riny_van_Eekelen
  Platinum Contributor
  May 15, 2022
  Beeeemster Of course it's your choice to use the very manual approach of Goal Seek for this, when you can have it done automatically with the straightforward formula I provided earlier. Demonstrated in the attached file.
  beeeemster.xlsx10 KB
  - Beeeemster
    Copper Contributor
    May 15, 2022
    Thanks Riny_van_Eekelen . Your solution is elegant :O)