# Thread: IF statement Thanks:  4 Post #5326517 (1)Post #5326595 (1)Post #5326599 (1)Post #5326611 (1) Likes:  2 Post #5326599 (1)Post #5326611 (1)

1. ## 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. ## 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?

3. ## 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,

4. ## 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. ## 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. ## 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. ## 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. ## 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

9. ## Re: Need help with IF statement

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. ## 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