+ Reply to Thread
Results 1 to 10 of 10

Cant figure out correct formula

  1. #1
    Registered User
    Join Date
    12-02-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    4

    Question Cant figure out correct formula

    Im creating a mileage sheet for my company and am having a bit of a time writing a formula. Heres my issue...

    Column C is beginning mileage, D is ending mileage, E is business miles driven, F personal miles driven and G the total miles driven.

    I have a formula in cell G10 to calculate the total miles driven for the day...=IF(OR(ISBLANK(D10),ISBLANK(C10)),"",D10-C10)

    Simple right? My problem is that I need also make sure that columns E+F equal Column G for their respective cells. This is to make sure that business miles and personal miles equal the total miles driven for the day and minimize any inadvertent errors in being entered.

    For some reason, I cant figure this one out. I've racked my brain for a good couple days with many error messages along the way.

    Any help would be greatly appreciated!

    Cheers and thanks....

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Cant figure out correct formula

    .
    From your description, this is how I would approach it :


    A
    B
    C
    D
    E
    F
    G
    1
    Beginning
    Ending
    Business
    Personal
    Total
    2
    3
    4
    5
    6
    7
    8
    9
    10
    Total :
    11
    12
    In E2 copied down : =IF(D2="","",(D2-C2))
    13
    In G2 copied down :=IF(D2="","",SUM(E2:F2))
    14
    In G10 : =SUM(E2:F2)

  3. #3
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Cant figure out correct formula

    Maybe: in F10

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  4. #4
    Registered User
    Join Date
    12-02-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    4

    Re: Cant figure out correct formula

    Thank you for your reply! It is helpful but I already have a formula in G10. I guess I didn't mention that in my original post. Im confused on how to add a formula like the one you suggested to a cell that already has a formula...or how to rewrite it to incorporate both.

    Any suggestions?


    Once again, thanks for taking the time. I appreciate it.

  5. #5
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Cant figure out correct formula

    Thanks for the rep! Can you:

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  6. #6
    Registered User
    Join Date
    12-02-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    4

    Re: Cant figure out correct formula

    Here is an example sheet as per your request. As you will see, the formula in cell G2 tabulates the total miles for the day. I need it to also say that if E+F dont equal that value, error message.
    Attached Files Attached Files

  7. #7
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Cant figure out correct formula

    Do you have any way to attribute the mileage to business or personal use?

  8. #8
    Registered User
    Join Date
    12-02-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    4

    Re: Cant figure out correct formula

    no other than entering it into the designated column/cell. They all add up into "Grand Totals" cells at the end of the sheet, but there's no way (so far) for me to make sure that Business+Personal equals the Total for each day. In the example I provided, one could enter 80 business and 30 personal. they would total in the grand total cell as 110 but only 100 miles were driven. Make sense?

  9. #9
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Cant figure out correct formula

    This works in G2 but may not be what you want:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Cant figure out correct formula

    Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

    Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

    To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (note: this change is not optional )
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Cannot get correct % figure on this
    By scudo in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-02-2016, 05:43 PM
  2. Look Up table not pulling correct figure
    By ROBOKOP in forum Excel General
    Replies: 3
    Last Post: 03-20-2016, 05:18 AM
  3. Replies: 4
    Last Post: 07-27-2015, 07:27 AM
  4. Replies: 3
    Last Post: 10-10-2014, 08:14 AM
  5. [SOLVED] Trying to figure out a way to correct dates in 8000 line spreadsheet
    By ppilot in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-10-2014, 05:27 PM
  6. [SOLVED] Bar Chart Data label % figure not correct
    By Lsinshirley in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-03-2013, 09:59 AM
  7. [SOLVED] A correct formula gives an incorrect figure
    By GarMcCas in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-17-2006, 06:55 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1