Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for Mileage / MPG Tracking formula
I'm trying to build a daily mileage spreadsheet that will allow me to enter
my daily mileage, and will use these numbers to calculate Miles Per Gallon on the days when I fill up the tank (1-2 days per week). So, column headings are as such: A-----B---------C--------------D---------------E-----F Date, Odometer, Miles Driven, Gallons Used, MPG, Average MPG 1/22, 0000 1/23, 0365 1/24, 0724 1/25, 0912, 912, 74, 12.32 1/28, 1215 1/29, 1526 1/30, 1797, 885, 76, 11.64 .. .. .. How could I put together a formula that will subtract the odometer reading on 1/30 from the last fillup, whenever that was, to calculate the mpg? Any information would be helpful. Thanks! -- Wayne B - MN |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for Mileage / MPG Tracking formula
Try this:
I would move your columns around slightly: Column C = Gallons Used Column D = Miles Driven Place a 0 in cell C2 as your initial entry for Gallons Used Then, enter this formula in cell D2 under Miles Driven: This is an array formula and needs to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =IF(C2=0,"",B2-LARGE(IF(C$2:C2<"",B$2:B2),2)) Enter this formula in E2 under MPG: =IF(COUNT(C2:D2)<2,"",D2/C2) Format as NUMBER 2 decimal places Select both D2 and E2 and copy down as needed. Biff "WayneB" wrote in message ... I'm trying to build a daily mileage spreadsheet that will allow me to enter my daily mileage, and will use these numbers to calculate Miles Per Gallon on the days when I fill up the tank (1-2 days per week). So, column headings are as such: A-----B---------C--------------D---------------E-----F Date, Odometer, Miles Driven, Gallons Used, MPG, Average MPG 1/22, 0000 1/23, 0365 1/24, 0724 1/25, 0912, 912, 74, 12.32 1/28, 1215 1/29, 1526 1/30, 1797, 885, 76, 11.64 . . . How could I put together a formula that will subtract the odometer reading on 1/30 from the last fillup, whenever that was, to calculate the mpg? Any information would be helpful. Thanks! -- Wayne B - MN |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for Mileage / MPG Tracking formula
That seems to be doing the trick,
Thanks for your help! -- Wayne B - MN "T. Valko" wrote: Try this: I would move your columns around slightly: Column C = Gallons Used Column D = Miles Driven Place a 0 in cell C2 as your initial entry for Gallons Used Then, enter this formula in cell D2 under Miles Driven: This is an array formula and needs to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =IF(C2=0,"",B2-LARGE(IF(C$2:C2<"",B$2:B2),2)) Enter this formula in E2 under MPG: =IF(COUNT(C2:D2)<2,"",D2/C2) Format as NUMBER 2 decimal places Select both D2 and E2 and copy down as needed. Biff "WayneB" wrote in message ... I'm trying to build a daily mileage spreadsheet that will allow me to enter my daily mileage, and will use these numbers to calculate Miles Per Gallon on the days when I fill up the tank (1-2 days per week). So, column headings are as such: A-----B---------C--------------D---------------E-----F Date, Odometer, Miles Driven, Gallons Used, MPG, Average MPG 1/22, 0000 1/23, 0365 1/24, 0724 1/25, 0912, 912, 74, 12.32 1/28, 1215 1/29, 1526 1/30, 1797, 885, 76, 11.64 . . . How could I put together a formula that will subtract the odometer reading on 1/30 from the last fillup, whenever that was, to calculate the mpg? Any information would be helpful. Thanks! -- Wayne B - MN |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for Mileage / MPG Tracking formula
You're welcome. Thanks for the feedback!
Biff "WayneB" wrote in message ... That seems to be doing the trick, Thanks for your help! -- Wayne B - MN "T. Valko" wrote: Try this: I would move your columns around slightly: Column C = Gallons Used Column D = Miles Driven Place a 0 in cell C2 as your initial entry for Gallons Used Then, enter this formula in cell D2 under Miles Driven: This is an array formula and needs to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =IF(C2=0,"",B2-LARGE(IF(C$2:C2<"",B$2:B2),2)) Enter this formula in E2 under MPG: =IF(COUNT(C2:D2)<2,"",D2/C2) Format as NUMBER 2 decimal places Select both D2 and E2 and copy down as needed. Biff "WayneB" wrote in message ... I'm trying to build a daily mileage spreadsheet that will allow me to enter my daily mileage, and will use these numbers to calculate Miles Per Gallon on the days when I fill up the tank (1-2 days per week). So, column headings are as such: A-----B---------C--------------D---------------E-----F Date, Odometer, Miles Driven, Gallons Used, MPG, Average MPG 1/22, 0000 1/23, 0365 1/24, 0724 1/25, 0912, 912, 74, 12.32 1/28, 1215 1/29, 1526 1/30, 1797, 885, 76, 11.64 . . . How could I put together a formula that will subtract the odometer reading on 1/30 from the last fillup, whenever that was, to calculate the mpg? Any information would be helpful. Thanks! -- Wayne B - MN |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for Mileage from Latitude and Longitude | Excel Discussion (Misc queries) | |||
Is there a Mileage Tracking function? | Excel Worksheet Functions | |||
Mod Formula for Mileage? | Excel Worksheet Functions | |||
Mileage Claim Formula | New Users to Excel | |||
mileage reimbursement formula | Excel Discussion (Misc queries) |