Formula for MPG with dynamic distance variable

[britomart]

Hi everyone. I'm horrible with math and am very rusty at Excel. I apologize in advance for asking such an easy question and my ignorance.

I'm using Excel '07 and trying to compute the monthly cost of my drive to work.

The problem is that I need to change the distance traveled per computation. I have about 4 sheets that utilize different distances. The distance is placed in cell B4 on each sheet.

So, this is what I've come up with but I can't seem to create an Excel formula. Any help will be greatly welcomed.

Distance is X (let's say 32 miles for example). Gas is $2.70 per gallon. My car gets 30 mpg. I use 2-4 gallons per day.

First, I'd calculate the daily amount then the amount of days traveled within the month to calculate the monthly cost.

So, this is what I worked out in my addled brain:

=B4(2.7*2)*21.5 (the number of days driven to work)

However, I come out with a total of over $3,000. What am I doing wrong? What would the correct formula be?

Thank you for any help. I'll greatly, greatly appreciate it.

 

[Blade Hunter]

Something like this??

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:131px;" /><col style="width:93px;" /><col style="width:93px;" /><col style="width:93px;" /><col style="width:93px;" /><col style="width:93px;" /><col style="width:93px;" /><col style="width:93px;" /><col style="width:93px;" /><col style="width:93px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Days driven to work</td><td >Distance</td><td >MPG</td><td >Gas cost</td><td >Trip use</td><td >Trip Cost</td><td >Daily use</td><td >Daily cost</td><td >Monthly use</td><td >Monthly cost</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">                              21.50 </td><td style="text-align:right; ">                 32.00 </td><td style="text-align:right; ">                 30.00 </td><td style="text-align:right; "> $                2.70 </td><td style="text-align:right; ">                    1.07 </td><td style="text-align:right; "> $                2.88 </td><td style="text-align:right; ">                    2.13 </td><td style="text-align:right; "> $                5.76 </td><td style="text-align:right; ">                 45.87 </td><td style="text-align:right; "> $            123.84 </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E2</td><td >=(1/C2)*B2</td></tr><tr><td >F2</td><td >=(D2/C2)*B2</td></tr><tr><td >G2</td><td >=(<span style=' color:008000; '>(1/C2)</span>*B2)*2</td></tr><tr><td >H2</td><td >=(<span style=' color:008000; '>(D2/C2)</span>*B2)*2</td></tr><tr><td >I2</td><td >=(<span style=' color:008000; '>(<span style=' color:#0000ff; '>(1/C2)</span>*B2)</span>*2)*A2</td></tr><tr><td >J2</td><td >=(<span style=' color:008000; '>(<span style=' color:#0000ff; '>(D2/C2)</span>*B2)</span>*2)*A2</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[britomart]

A little more detailed than I was expecting, but I think that will work!

Thanks a lot Blade. You saved me a huge migraine.