Hello all,
Hopefully I can explain this right.
I have a home made mileage/maintenance log.
1.) Column B has the dates I filled up with gas or had maintenance done etc. (One item per row).
2.) Column F has the Gallons added. (If no gas was bought, this cell is empty.)
3.) Column O is days between fill up.
My problem is figuring days between fill up. I am trying to write a formula in Column O (days between fill up) that will see if that row has a number in the gallons column (F) and if it does, find the first cell in F above that also has a number in it. then calc the number of days between the dates in column B, thus giving me my answer.
Is this possible? Have I overthought something? Am I going about this the wrong way? Does any of this make sense?
Any help would be appreciated.
Thanks!
Here is the minisheet (I hope)
Hopefully I can explain this right.
I have a home made mileage/maintenance log.
1.) Column B has the dates I filled up with gas or had maintenance done etc. (One item per row).
2.) Column F has the Gallons added. (If no gas was bought, this cell is empty.)
3.) Column O is days between fill up.
My problem is figuring days between fill up. I am trying to write a formula in Column O (days between fill up) that will see if that row has a number in the gallons column (F) and if it does, find the first cell in F above that also has a number in it. then calc the number of days between the dates in column B, thus giving me my answer.
Is this possible? Have I overthought something? Am I going about this the wrong way? Does any of this make sense?
Any help would be appreciated.
Thanks!
Here is the minisheet (I hope)
| New milage calculator in progress.xlsx | ||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | |||
| 3 | Date | Odometer Reading | Trip Meter | Odometer Miles | Gallons | Price per Gallon | Misc. Extra Gas Fee | Gas Cost | Service Class | Service provided / Item Purchased | Service Cost | Vendor | MPG | Days between fill up | Notes | test | ||
| 4 | 4/7/2016 | 135394.0 | 0.0 | #VALUE! | 0.000 | $0.000 | Purchase | Bought Truck | $4,500.00 | Private person | #VALUE! | Bought truck | TRUE | |||||
| 5 | 4/8/2016 | 135434.0 | 40.0 | 40.0 | 22.199 | $2.759 | $0.35 | $61.60 | Gas | Gas | Arco | na | 1 | TRUE | ||||
| 6 | 4/8/2016 | 135434.0 | 0.0 | 40.0 | Registration | Registration | $105.00 | DMV | FALSE | |||||||||
| 7 | 4/8/2016 | 135434.0 | 0.0 | 40.0 | Smog Test | Smog Test | $48.95 | Express Lube | FALSE | |||||||||
| 8 | 4/9/2016 | 135434.0 | 0.0 | 40.0 | Upgrade | Wiring Harness | $13.07 | Auto Zone | FALSE | |||||||||
| 9 | 4/9/2016 | 135434.0 | 0.0 | 40.0 | Maintenance | Maint. Manuals | $65.37 | Pep Boys | FALSE | |||||||||
| 10 | 4/9/2016 | 135434.0 | 0.0 | 40.0 | Cosmetic | Door Guards | $6.52 | Pep Boys | FALSE | |||||||||
| 11 | 4/9/2016 | 135434.0 | 0.0 | 40.0 | Maintenance | Neg. Battery Wire | $7.61 | Pep Boys | FALSE | |||||||||
| 12 | 4/10/2016 | 135434.0 | 0.0 | 40.0 | Maintenance | Hood lifters | $43.58 | Pep Boys | FALSE | |||||||||
| 13 | 4/10/2016 | 135434.0 | 0.0 | 40.0 | Maintenance | $ New Tires | $670.00 | Western Tire | FALSE | |||||||||
| 14 | 4/12/2016 | 135434.0 | 0.0 | 40.0 | Cosmetic | Blind spot mirrors | $4.35 | Pep Boys | FALSE | |||||||||
| 15 | 4/12/2016 | 135434.0 | 0.0 | 40.0 | Security | Tailgate Lock | $21.79 | Pep Boys | FALSE | |||||||||
| 16 | 4/13/2016 | 135434.0 | 0.0 | 40.0 | Insurance | Insurance | $80.00 | AAA | FALSE | |||||||||
| 17 | 4/13/2016 | 135434.0 | 0.0 | 40.0 | Maintenance | Spare Tire | $30.00 | Tony's Tires | FALSE | |||||||||
| 18 | 4/14/2016 | 135434.0 | 0.0 | 40.0 | Maintenance | Spare Tire | $35.00 | Diamond Tires | FALSE | |||||||||
| 19 | 4/14/2016 | 135434.0 | 0.0 | 40.0 | Maintenance | Lug Wrench | $9.79 | Pep Boys | FALSE | |||||||||
| 20 | 4/15/2016 | 135434.0 | 0.0 | 40.0 | Cosmetic | Center console | $16.34 | Pep Boys | FALSE | |||||||||
| 21 | 4/15/2016 | 135600.0 | 0.0 | 166.0 | 12.524 | $2.699 | $0.35 | $34.15 | Gas | Gas | Arco | 13.255 | TRUE | |||||
| 22 | 4/17/2016 | 135718.0 | 0.0 | 118.0 | 7.870 | $2.699 | $0.35 | $21.59 | Gas | Gas | Arco | 14.994 | 2 | TRUE | ||||
| 23 | 4/19/2016 | 135718.0 | 0.0 | 118.0 | Maintenance | Spark Plugs | $14.32 | Pep Boys | FALSE | |||||||||
| 24 | 4/19/2016 | 135718.0 | 0.0 | 118.0 | Maintenance | Antenna | $10.89 | Pep Boys | FALSE | |||||||||
| 25 | 4/19/2016 | 135718.0 | 0.0 | 118.0 | Maintenance | Spare tire tools | $6.52 | Pep Boys | FALSE | |||||||||
| 26 | 4/20/2016 | 135718.0 | 0.0 | 118.0 | Maintenance | Recirculating Door | $33.08 | Amazon | FALSE | |||||||||
| 27 | 4/22/2016 | 135718.0 | 0.0 | 118.0 | Maintenance | Door lock actuators (2) | $9.55 | Amazon | FALSE | |||||||||
| 28 | 4/25/2016 | 135718.0 | 0.0 | 118.0 | Maintenance | Reverse Light bulbs & Window handle retaining clips | $10.88 | Pep Boys | FALSE | |||||||||
| 29 | 4/27/2016 | 135718.0 | 0.0 | 118.0 | Maintenance | Reverse Light bulbs | $6.53 | Pep Boys | FALSE | |||||||||
| 30 | 4/30/2016 | 136071.0 | 353.0 | 353.0 | 25.015 | $2.699 | $0.35 | $67.87 | Gas | Gas | Arco | 14.112 | 3 | TRUE | ||||
| 31 | 5/21/2016 | 136402.0 | 331.0 | 331.0 | 25.081 | $2.699 | $0.35 | $68.04 | Gas | Gas | Arco | 13.197 | 21 | TRUE | ||||
| 32 | 6/5/2016 | 136755.0 | 352.8 | 353.0 | 24.900 | $2.699 | $0.35 | $67.56 | Gas | Gas | Arco | 14.177 | 15 | TRUE | ||||
| 33 | 6/21/2016 | 137127.0 | 371.7 | 372.0 | 24.355 | $2.739 | $0.35 | $67.06 | Gas | Gas | Arco | 15.274 | 16 | TRUE | ||||
Data | ||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| N4,N6:N33 | N4 | =IFERROR(E4/F4,"") |
| O4:O33 | O4 | =IF([@Gallons]="","",IF([@Date]-B3=0,"",[@Date]-B3)) |
| E4:E33 | E4 | =IF(C4-C3=0,E3,C4-C3) |
| I4:I33 | I4 | =IF(([@[Price per Gallon]]*[@Gallons])+[@[Misc. Extra Gas Fee]]=0,"",([@[Price per Gallon]]*[@Gallons])+[@[Misc. Extra Gas Fee]]) |
| Q4:Q33 | Q4 | =ISNUMBER(F4) |
