Status
Not open for further replies.
1 - 18 of 18 Posts

#### valis

·
##### Super Moderator
Joined
·
80,081 Posts
Discussion Starter · ·
wife got a job as a courier, want to track miles travelled, deliveries made, etc et al ad nauseum. Only quirk in the sheet is that I want to track her mileage as well, and then only on the days that she fills up. I want this to be as simple as possible, so that all she has to do is plug in the ending mileage travelled (beginning mileage will default to the ending of the previous day), and the mpg is something I just want to track for the heck of it. We need to track the gas for tax purposes regardless, so I am going to have access to the gallons via receipts, but the kicker is that, again, I only want the cell to activate when something is entered into the gallons column.

Give me a few to work out a template and I'll attach it, but I hope that's clear enough.

thanks,

v

#### OBP

·
##### Registered
Joined
·
19,932 Posts
valis, can't you use Access for this?

#### valis

·
##### Super Moderator
Joined
·
80,081 Posts
Discussion Starter · ·
OBP said:
valis, can't you use Access for this?
probably. WAAAAY more familiar with excel than access, though, and the only part that needs conditional calculation is the mpg column, which I ONLY want calculated when she actually gets gas.

#### Zack Barresse

·
##### Registered
Joined
·
5,459 Posts
So you're just looking for a mpg formula? Assuming that your starting mileage is in column B (we'll say your data starts on row 2), also need to assume that you will not calculate the first row (row 2) of data as you do not have a start prior to it, and gallons in column C, you might try something like this in D3...
Code:
``=(B3-B2)/C3``
That will give you miles / gallon. Is that what you're looking for?

And depending on what you'll want to do with this, i.e. reporting, Access may indeed be better off. But I completely understand about comfortability.

#### valis

·
##### Super Moderator
Joined
·
80,081 Posts
Discussion Starter · ·
no, really all this is a chart to track her \$ spent on gas, and miles driven, as she will get a percentage paid on mileage. Me, being an engineer, would like to measure a few things as well, as I have all the tools here to do it. Attached find a very basic template for this.

Again, it's no big, but I know that with excel, if you get one div/0 error, the chart doesn't like that, and I would like to chart her mpg over time, along with a few other things. God alone in his omniscience knows what this is going to grow into. I should show you what Frankensteinian monster evolved from a spreadsheet to keep track of my sister's and I scrabble games over the internet.

#### Attachments

• 31 KB Views: 56

#### bomb #21

·
##### Registered
Joined
·
8,565 Posts
valis said:
probably. WAAAAY more familiar with excel than access, though, and the only part that needs conditional calculation is the mpg column, which I ONLY want calculated when she actually gets gas.
OK, allow me to be dense here but ... why would you do that? Surely mpg over time would be a single calc of current mileage minus original mileage over sum of gallons?

Still can't believe that "fuel added for an additional 15 laps" on the second stop tho'.

#### valis

·
##### Super Moderator
Joined
·
80,081 Posts
Discussion Starter · ·
bomb #21 said:
OK, allow me to be dense here but ... why would you do that? Surely mpg over time would be a single calc of current mileage minus original mileage over sum of gallons?

Still can't believe that "fuel added for an additional 15 laps" on the second stop tho'.
because she, unlike Ron Dennis, won't be filling up every day. So only on the days that she puts gas in, do I want the MPG cell activated. I can't just do a simple d1/e1, for on the days she doesn't get fuel, it will return the div/0 error and the chart gets hosed. So it's going to need to be a conditional formula, methinks.

#### bomb #21

·
##### Registered
Joined
·
8,565 Posts
(EDIT: my actual point was why have MPG at each fuelling rather than a single average figure since records began; but I won't labour it, so ...)

a possible syntax for a conditional formula might be:

=IF(G5="","",F5/G5)

but ... wouldn't you actually want mileage since the last fuelling over fuel added?

Either way, good luck with the job.

#### slurpee55

·
##### Registered
Joined
·
7,842 Posts
If you had a running sum of the miles traveled, then something simple, like
=IF(G5>0,F5/G5, " ") would suffice. But you would have to start the sum over each time you bought gas. You could have a running total of miles (like D6-\$C5) and divide by the sum of the fuel purchased also, to get mileage over time....
Oh, just read bomb's post - duh, said what he did, more or less....

#### Zack Barresse

·
##### Registered
Joined
·
5,459 Posts
Instead of null ( "" ), why not use 0 and do a logarithmic trend?

Edit: Had an example to post for you.

#### Attachments

• 57.5 KB Views: 69

#### valis

·
##### Super Moderator
Joined
·
80,081 Posts
Discussion Starter · ·
What's the iserr formula do? Looks like it does the trick, as what I needed was something that wouldn't return a 'div/0' error when a null was entered in the gallons field, and that seems to work.

Thanks, fire.....howdy to oregon for me...lived in portland for a few years.

let me monkey with and google iserr and I'll probably mark this one solved...

#### Zack Barresse

·
##### Registered
Joined
·
5,459 Posts
ISERR() is a shortened version of ISERROR() function. A Boolean return will test a condition and return True or False if the condition is an error. Note that it will not return a True value if #N/A is returned. In charting, the #N/A error can be skipped over. Check out Tushar Mehta's site for a good add-in for charting #N/A's as blanks.

Oregon is good! I lived in Vancouver for a while, spent most of my time in the Gorge though, both Oregon and Washington sides.

#### valis

·
##### Super Moderator
Joined
·
80,081 Posts
Discussion Starter · ·
thanks, man, will do....use excel a lot, was really specialized in it about 4 years ago, now am more of the network side, so don't get to play with it as much as i like to.....still a fun app, vastly underrated, and I can generally make it do what I want....will check out the link, and thanks again....marking solved.

#### slurpee55

·
##### Registered
Joined
·
7,842 Posts
Zack, that formula still only delivers the mileage as if you had traveled all your miles on one day - I just put in some numbers that over 3 days added to 75 miles, then added 20 gallons - got an mpg of 1.35. whereas 75 miles divided by 20 gallons should be 3.75.
(see posts 8 and 9 referencing a running sum since last fueled...maybe use HLOOKUP to find the last entry and subtract the difference?)

#### Zack Barresse

·
##### Registered
Joined
·
5,459 Posts
True. I assumed that when miles were to be entered the user would fill up with fuel, thus not leaving an entered (positive) mileage run without an equivalent fuel figure. If this will be the case then we'll need to re-think the formula scheme. Valis, will this be the case?

If I am correct in my assumptions, I would add conditional formatting to column B (Miles Traveled column, I changed the columns around for charting purposes, see example) ...

Code:
``=(ROW()>4)*(\$C1=0)*(\$B1>0)*(\$E1<>"")``
Take a look at the supplied sample file, Valis, let us know what we need to do for you.

#### Attachments

• 41 KB Views: 47

#### valis

·
##### Super Moderator
Joined
·
80,081 Posts
Discussion Starter · ·
so far, the first one is working fine....only glitch I've run into so far is she stopped to put in \$10 this morning, then filled up this evening, effectively throwing the entire thing into chaos.

But again, so far the first one is working fine. Again, I'm only checking the mileage for my own purposes, and all she needs to track is \$ of gas, deliveries, and oil changes/misc repairs, which the company will spring for. It'll get interesting as we figure out the % of gas she gets paid back, which is NOT static per delivery, but instead is determined by the company she delivers to. If I can get a handle on whether or not there's a consistent amount of % per type of company, then I can just toss in a vlookup and enter 1 for hospital, 2 for luggage, 3 for computer stuff, etc....but I don't think it's going to be like that....

again, thanks for your help, and I'll keep you posted.

v

#### slurpee55

·
##### Registered
Joined
·
7,842 Posts
I figured that since you said
valis said:
I want to track her mileage as well, and then only on the days that she fills up.
you would want to do a mileage since last fillup, since the
beginning mileage will default to the ending of the previous day
would not give you a real mpg figure.

1 - 18 of 18 Posts
Status
Not open for further replies.