leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
611
Office Version
  1. 2016
Platform
  1. Windows
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.


Book1
BCDEFGHIJ
201WaypointsOdometerDriving Time
202Travel DateStartFinishStartFinishStartFinishDrive HoursMileage
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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:
Upvote 0
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"))


BCDEFGHIJKL
201Waypoints
Odometer
Driving Time
202Travel Date
StartFinish
StartFinishStartFinishDrive HoursMileageOoH KmOoH Miles
2031/1/2019HomeRaymond, WA20135205667:0013:356.626800.00
2041/2/2019Raymond, WAFlorence, OR20598209707:1212:555.72313219.88
2051/3/2019Florence, ORCrescent City, CA21016213008:3012:003.51764628.58

<tbody>
</tbody>

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

Regards,
Toadstool
 
Last edited:
Upvote 0
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
Cell Formulas
RangeFormula
K202=SUM(K203:K1000)
K204=E204-F203
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
Re: Need help with IF statement

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

ABCDEFGHIJ
201WaypointsOdometerDriving Time
202Travel DateStartFinishStartFinishStartFinishDrive HoursMileage
2031/1/2019HomeRaymond, WA20135205667:0013:356.6268
2041/2/2019Raymond, WAFlorence, OR20598209707:1212:555.7231
2051/3/2019Florence, ORCrescent City, CA21016213008:3012:003.5176
206
22778

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1 (2)

Array Formulas
CellFormula
J227{=IF(E204:E226<>"",SUM(E204:E226-OFFSET(E204:E226,-1,1))+MAX(F204:F226))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top