# Solved: should be simple excel stuff

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### valis

Moderator
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

valis, can't you use Access for this?

#### valis

Moderator
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

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

Moderator
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

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

Moderator
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

(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

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

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

Moderator
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

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

Moderator
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

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?)

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

As Seen On