Forum Discussion

Beeeemster's avatar
Beeeemster
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's avatar
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

 

x1.278409 
   
   
 offon
 1.2784094.721591
Rates0.0750.3566
   
subtotal0.0958811.683719
   
Total 1.7796

 

 

 

  • Beeeemster's avatar
    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's avatar
      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's avatar
        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.

Resources