There are two elements of expense to consider when you fill your car’s tank with gas.  The first one is obvious.  It is the cost per gallon for the gasoline itself.  The other factor is the cost of operating your car.  Both factors contribute to the overall expense of filling your tank.

The price of gas is an important factor in deciding where to make your next purchase.  You would not consider driving 100 miles just to save one cent on each gallon of gas you buy, so consider the following.

There are eight gas stations near your home.  Each station sells gas for a slightly different price and each station is located at a different distance from your home.  Driving costs money!.  For this exercise, we will use the following assumptions:

·         The gas tank in your car holds exactly 21 gallons.

·         Your gas gauge tells you exactly how much gas is in your tank.

·         You can travel a distance of 15 miles for each gallon of gas in the car’s tank.  (15 MPG)

·         It costs $.50 per mile to operate your car.

·         It will cost you $100 for a tow if you run out of gas.

This is a list of the gas stations near your home.

Gas Station                 Price (Per Gallon)             Distance (Miles)
Joe’s    $1.9    6.5
 
Bill’s    $1.7    8.0
 
Ed’s    $1.92    11.0
 
Sue’s    $1.66    9.0
 
Sally’s    $1.56    17.0
 
Tom’s    $1.38    18.0
 
Martha’s   $1.75    7.2
 
Frank’s    $2.0    2.0
 

 

Create a spreadsheet to help you answer the following questions:

1.       At which gas station will you find the best overall deal when you buy 10 gallons of gas?  (worth 3 points)

2.       If you need 15 gallons of gas, where is it least expensive overall to buy it?  (worth 3 points)

3.       If your gas gauge indicates you need 20 gallons to fill up the tank, which gas station will be the least expensive overall?  (worth 4 points)

To the right of each of the 3 columns that show the total cost to purchase gas add a column that automatically indicates which of the gas stations is the least expensive for each of the three previous questions.  Have it display a word like “Cheapest” or “Minimum” to indicate the station with the lowest total cost for that case.

somebody wants his/her homework be done by sajhaites. what a lame student?

Jacksparrow007,

Try it by yourself first,if you have any specific problem contact me.

The answers as per my calculations are
For 10 gallons = Frank's
For 15 gallons = Sue's
For 20 gallons = Bill's

Gas Station	Per Gallon	Distance (miles)	Cost of Operating
Joe’s	1.90	6.5	$44.53
Bill’s	1.70	8	$42.03
Ed’s	1.92	11	$49.45
Sue’s	1.66	9	$42.24
Sally’s	1.56	17	$48.26
Martha’s	1.75	7.2	$42.23
Frank’s	2.00	2	$42.01
Tom’s	1.38	18	$55.96
Assumption: In weekend you going to fill in gas and coming home.
Therefore the operation is to and fro and gas filled in is assumed there is one gallon of gas.
How much galllon is added, assumption is there is 1 gallon of gas and will go upto 15 miles.
To go to Tom's Gas station, fill in gas at Sally's station atlest one gallon to avoid towing cost.
Suggested to go to Frank's Gas Station.
Gas Station	Per Gallon	Distance (miles)	Cost of Operating
Joe’s	1.90	6.5	$42.07
Bill’s	1.70	8	$38.91
Ed’s	1.92	11	$45.31
Sue’s	1.66	9	$38.70
Sally’s	1.56	17	$41.47
Martha’s	1.75	7.2	$39.44
Frank’s	2.00	2	$41.27
Tom’s	1.38	18	$41.92
Assumptions: Working days filling gas on the way to work.
It is cheaper to fill in Sue's gas station.

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.

 

as far as jhapali thito calling me lame student, i dont have any thing to say, but just wanted to tell all the sajha user, today is my due date n just wanna match answer with u guys, there is nothing being lame student, just utilizing sajhas, in dis way i can save my pints

Silpat Kura: You should have mentioned if you were close to my answer? There there are assumption and I tried to match most of them.

Assumption: 1) The car drives upto 15 miles the gas in the tank and assumed that the car gives 15 miles per gallon.
2) Total operating cost to fill in the whole tank as it said shows how much gas it will be filled.
3) Calculation is done according to the distance it is traveled to various gas stations.
4) Going back it will cost gas as well cost .50 per mile operating cost.
Did not add back the cost of gas going back to home.
Most constraints are trying to  considered to come with a realist value.

Now Are you close to any of us? What is your answer?? How did you do?
Should have written here rather than crappy answer.

Hey guys, I do not know if we can do this in excel... if it is possible, can you teach me how to ? here is a situation.

I keep a log of my expenses in excel. Column A has date on it. There will be multiple entries in one date, for instance, row 2,3,4 used today. Column B has the field I spend the money in, like, transportation, breakfast, lunch, pizza, chinese food, cigarette, and such. I did not assign a column to these items (to enter the values only in the corresponding columns) because I spend money in too many different things, so I thought it would be better if I write the name itself. Column C has the price for each field in column B.

Now I want to create a new sheet in the same file  to narrow down the cost of one field.. like say coffee cost. Is it possible to selectively pull the data from the first sheet to the second one? for instance, the third entry for today's date is $2.00 for coffee. I enter 4/21/2009 in Column A, Coffee in Column B and Column C has $2.00. No coffee was purchased yesterday but was the first entry on day before yesterday. In the separate sheet, I want to (I wish i could) compile the expenses on coffee only for the month. If possible, the second sheet would have different dates in Column A, 'coffee' in Column B and its cost in Column C.

If anybody knows how to do it, or if anybody knows if it is NOT possible to do it in excel, ..can you please share..... thanks

?

Copycat: Could not understand your question. Send me your excel Question, your spreadsheet and how you tried it and what kind of spreadsheet you want. Let me see if I have any thing to suggest you. Jhapalithito was right about this guy, he wanted an homework help, even did not bother to drop a line if our answer was helpful. Silpat may be a lame student??????????????

Post your spread sheet here, probably i might of help.

प्रश्न पुन: राख्न प्रयास गर्दैछु

Excel को एउटा फाइल छ, ' खर्च विवरण'  नाम गरेको । त्यसमा, sheet 1  यस्तो छ ।

date	field	amount
04/01/2009	Lunch	$8.50
04/09/2009	Muffin	$1.50
04/09/2009	bus	$5.75
04/09/2009	pizza	$6.00
04/10/2009	bus	$5.75
04/10/2009	Lunch	$7.43
04/11/2009	bus	$2.75
04/11/2009	Lunch	$9.78
04/11/2009	beer	$25.00
04/11/2009	lottery	$3.00
04/11/2009	junk food	$7.62

Sheet 2 यस्तो छ । यो मैले manually enter गरेको हुँ , sheet 2 मा. lunch खर्च मात्र राख्न चाहेको हुँ, यसमा

04/01/2009	Lunch	$8.50
04/10/2009	Lunch	$7.43
04/11/2009	Lunch	$9.78

यदि मैले Sheet 2 को A1 position मा ' =Sheet1!A2'  लेखेँ भने उताको कपि भएर यता आउँछ । त्यसै गरि अरु सेल हरु पनि कपि गर्न सकिन्छ । तर मलाइ lunch खर्च मात्र राख्ने इच्छा छ, यस सिट मा - माथि देखाएको जस्तो ।

के यो सम्भव छ ? छ भने कसरि गर्ने?

?

what abt auto filters??

I think it can help you

Copycat,

well you have couple of option for doing that.

1) you can use autofilter as suggested by dd_sh and paste the values in each and every sheet. But then again after you insert any record in sheet 1 , you  have to repeat it. This way is good only if you wanna see the result in sheet 2 once a month lets say. Its pretty easy way.

2) if you are comfortable with access, you can import your existing record into Ms Acess: Actually its good idea even to store your data in access henceforth.Then all you have to do is make a simple query to display as you want. u can use GUI query or if you are comfortable with SQL, use sql. Choice is yours.

3) If you are adamant that  want to do that in excel, there is a better way than doing in Excel than autofilter which will update list in sheet 2 as you enter values in sheet 1. The idea is to extract the Lunch fields from sheet 1 using functions like if, rows, vlookup etc. And then further using Vlookup base in date field. This way, you have to write formulas which might seem to a bit complicated, but once u grasp it , its easy and u may use it further for other stuff. Look at this link , it will greatly help you to see what i m taking abt..

https://www.youtube.com/watch?v=nz9LcymyjbY

Either way you can easily do what u wanted to do.