Posted by: syanjali April 21, 2009
excel genius
Login in to Rate this Post:     0       ?        

Hi; Here is the new solution.


























































Gas Station                


Price/Gal


Miles


Total Operating Cost


Frank’s   


2.00


2.00


$42.53


 


Joe’s    


1.90


6.50


$46.15


 


Martha’s  


1.75


7.20


$43.88


 


Bill’s  


1.70


8.00


$43.81


 


Sue’s   


1.66


9.00


$44.19


 


Ed’s  


1.92


11.00


$52.22


 


Sally’s    


1.56


17.00


$40.08


 


Tom’s   


1.38


18.00


$41.21


 


Assumptions: Filling in weekends back and forth operating cost.


To avoid Toing cost, filled 1 gallon gas at Sue's gag station and proceed to sally's and tom's.


Added fuel cost to go to home, that is additional cost would have saved if filled in closer gas station


While filling gas, at sally's how much gas will be filled is calculated this way ( 21-((15+15)-17)/15),


which is ((30-C8)/15) and multiplied by cost of gas per gallan at that gas station.


To avoid towing cost, one gallon of gas is added at Sue's gas station and added to the total cost.


Here is the formula;






















































Gas Station                 Price/Gal Miles Total Operating Cost
Frank’s    2 2 =(C2*0.5)*2+((21-(1-C2/15))*B2)+(C2/15)*B2
Joe’s     1.9 6.5 =(C3*0.5)*2+((21-(1-C3/15))*B3)+(C3/15)*B3
Martha’s   1.75 7.2 =(C4*0.5)*2+((21-(1-C4/15))*B4)+(C4/15)*B4
Bill’s   1.7 8 =(C5*0.5)*2+((21-(1-C5/15))*B5)+(C5/15)*B5
Sue’s    1.66 9 =(C6*0.5)*2+((21-(1-C6/15))*B6)+(C6/15)*B6
Ed’s   1.92 11 =(C7*0.5)*2+((21-(1-C7/15))*B7)+(C7/15)*B7
Sally’s     1.56 17 =(B6*1)+(C8*0.5*2)+((21-((30-C8)/15)*B8))+(C8/15)*B8
Tom’s    1.38 18 =(B6*1)+(C9*0.5*2)+((21-((30-C9)/15)*B9))+(C9/15)*B9


This is my final answer and Tom's the cheapest.


 

Read Full Discussion Thread for this article