IF statement
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: IF statement

  1. #1
    Board Regular
    Join Date
    May 2007
    Location
    North Vancouver, BC, Canada
    Posts
    311
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default IF statement

    Hello forum friends, okay, here is an interesting one, at least from my perspective. I have been trying to come up with a formula that will look at two columns and return the difference between the odometer readings 'finish' from one day and the 'start' from the next day. This amount would be returned for each and every day as the user enters data in these two columns (columns E and F). See below.

    BCDEFGHIJ
    201WaypointsOdometerDriving Time
    202Travel DateStartFinishStartFinishStart Finish Drive Hours Mileage
    2032019-01-01HomeRaymond, WA201352056607:0013:356.6268
    2042019-01-02Raymond, WAFlorence, OR205982097007:1212:555.7231
    2052019-01-03Florence, ORCrescent City, CA210162130008:3012:003.5176

    2020



    So, if the formula works as desired, the amount returned after these three days of data was entered would be 78 ((20598-20566) + (21016-20970)). The columns are, of course, longer than this sample shown here and the formula in the cell in question will have to take that into account. There could be upwards of 30-50 entries. Pay no attention to the 'mileage' column, the odometer readings are in km's and then converted to miles. Thanks in advance for any help!

    Cheers

  2. #2
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    599
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help with IF statement

    From what you said, aren't you just doing: E204-F203 and filling that down?

    Where does the result (78 in this case) go?
    Last edited by kweaver; Aug 15th, 2019 at 05:40 PM.

  3. #3
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    206
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Need help with IF statement

    I assume you've already got formulae on each row for the Drive Hours and Mileage calculations so can you add another for Out of Hours kilometres?

    =IF(OR(NOT(ISNUMBER(K3)),E4=""),0,E4-F3)

    or even OoH Miles
    =IF(OR(NOT(ISNUMBER(K3)),E4=""),0,CONVERT((E4-F3)*1000,"m","mi"))


    B C D E F G H I J K L
    201 Waypoints
    Odometer
    Driving Time
    202 Travel Date
    Start Finish
    Start Finish Start Finish Drive Hours Mileage OoH Km OoH Miles
    203 1/1/2019 Home Raymond, WA 20135 20566 7:00 13:35 6.6 268 0 0.00
    204 1/2/2019 Raymond, WA Florence, OR 20598 20970 7:12 12:55 5.7 231 32 19.88
    205 1/3/2019 Florence, OR Crescent City, CA 21016 21300 8:30 12:00 3.5 176 46 28.58

    You can have a SUM at the end or even put the total in the heading.

    Regards,
    Toadstool
    Last edited by Toadstool; Aug 15th, 2019 at 05:46 PM.

  4. #4
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    599
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help with IF statement

    Excel 2010
    ABCDEFGHIJK
    201201WaypointsOdometerDriving Time
    202202Travel DateStartFinishStartFinishStartFinishDrive HoursMileage1878
    2032031/1/2019HomeRaymond, WA20135205667:0013:356.6268
    2042041/2/2019Raymond, WAFlorence, OR20598209707:1212:555.723132
    2052051/3/2019Florence, ORCrescent City, CA21016213008:3012:003.517646
    206Crescent City, CASomewhere, NV22600250001300
    207Somewhere, NVAnother Place, TX2550026500500

    PeopleCities



    Worksheet Formulas
    CellFormula
    K202=SUM(K203:K1000)
    K204=E204-F203


  5. #5
    Board Regular
    Join Date
    May 2007
    Location
    North Vancouver, BC, Canada
    Posts
    311
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help with IF statement

    Thanks to everyone who is trying to help me. I appreciate it.

    As usual, I didn't do a very good job of explaining myself and I apologize for that. I have a cell (J227) that is below the sample range that I posted in my original post, that we'll call 'Incidental mileage'. My intent is to have a formula in this cell that subtracts a particular day's START odometer reading and the previous day's FINISH odometer reading and adds this amount to a cumulative total within the same cell. The cell is static (doesn't move).

    For example, On January 1st, the user would enter their odometer reading before leaving home and then again when they arrived in Raymond, WA and J227 would be showing 0 miles because the trip just started. Now, imagine that while they are in Raymond, WA, they do a bit of exploring and go out for dinner somewhere, adding additional mileage to the vehicle. On January 2nd, they would enter their START odometer reading and cell J227 would do it's thing and calculate and display 32 miles as the incidental mileage that they accumulated while driving around in Raymond. Now, when they arrive in Florence, Oregon and enter their FINISH odometer reading. Now, this time they drive around a bit, maybe doing some sight-seeing in Florence for a total of 46 additional miles. On January 3rd, they enter their START odometer reading and now cell J227 would show 78 miles and it would continue to accumulate these 'incidental miles' every time the user enters a new pair of odometer readings in columns E & F. I'm pretty sure that there has to be a formula that can do this, but I keep running into errors (I'm not too bright). Appreciate any help!

    Cheers!

  6. #6
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    206
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Need help with IF statement

    leopardhawk,

    I think we've understood the problem and kweaver and myself suggested adding column K.

    Then in your J227 cell you can put =SUM(K203:K226) and it gives the total you seek.

  7. #7
    Board Regular
    Join Date
    May 2007
    Location
    North Vancouver, BC, Canada
    Posts
    311
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help with IF statement

    Okay, I didn't really want to add another column for aesthetic reasons and am still hopeful that there is a formula that will do what I am trying to accomplish. Thanks for reaching out!

    Cheers!

  8. #8
    Board Regular
    Join Date
    Feb 2018
    Location
    Oz
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Need help with IF statement

    G'day leopardhawk,

    Using the data you posted:
    Take the final odometer reading entered (F207) subtract the first odometer reading (E203) then subtract the summed logged distances.

    Cheers

    shane
    Be warned - I'm an Excel enthusiast, not an expert.

  9. #9
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    206
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Need help with IF statement

    Using a formula, no additional columns and the answer in j227.

    A B C D E F G H I J
    201 Waypoints Odometer Driving Time
    202 Travel Date Start Finish Start Finish Start Finish Drive Hours Mileage
    203 1/1/2019 Home Raymond, WA 20135 20566 7:00 13:35 6.6 268
    204 1/2/2019 Raymond, WA Florence, OR 20598 20970 7:12 12:55 5.7 231
    205 1/3/2019 Florence, OR Crescent City, CA 21016 21300 8:30 12:00 3.5 176
    206
    227 78
    Sheet1 (2)

    Array Formulas
    Cell Formula
    J227 {=IF(E204:E226<>"",SUM(E204:E226-OFFSET(E204:E226,-1,1))+MAX(F204:F226))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

  10. #10
    Board Regular
    Join Date
    May 2007
    Location
    North Vancouver, BC, Canada
    Posts
    311
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help with IF statement

    You, my friend, are a genius. This works perfectly and is exactly what I was hoping for...!! Thank you SO much...

    Cheers!

    p.s. many thanks to everyone else as well, I really appreciate it when people reach out to try and help whether it is successful or not.

    Sincerely,

    leopardhawk

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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