1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: should be simple excel stuff

Discussion in 'Business Applications' started by valis, Oct 27, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. valis

    valis Moderator Thread Starter

    Joined:
    Sep 24, 2004
    Messages:
    75,930
    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
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    valis, can't you use Access for this?
     
  3. valis

    valis Moderator Thread Starter

    Joined:
    Sep 24, 2004
    Messages:
    75,930
    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.
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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. ;)
     
  5. valis

    valis Moderator Thread Starter

    Joined:
    Sep 24, 2004
    Messages:
    75,930
    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. :)
     

    Attached Files:

  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    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'. :D
     
  7. valis

    valis Moderator Thread Starter

    Joined:
    Sep 24, 2004
    Messages:
    75,930
    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.
     
  8. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    (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. :)
     
  9. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    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.... :(
     
  10. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Instead of null ( "" ), why not use 0 and do a logarithmic trend?

    Edit: Had an example to post for you.
     

    Attached Files:

  11. valis

    valis Moderator Thread Starter

    Joined:
    Sep 24, 2004
    Messages:
    75,930
    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...:)
     
  12. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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. :)
     
  13. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
  14. valis

    valis Moderator Thread Starter

    Joined:
    Sep 24, 2004
    Messages:
    75,930
    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.
     
  15. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    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?)
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/644280

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice