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
 
Re: Need help with IF statement

Toadstool, one more question. When there is NO data in columns E & F, the formula returns FALSE which is not a big deal but I would like it better if it just showed a 0 (zero). To that end, I added IFERROR to the formula (see below) but am still getting the FALSE. Is it possible to have it return a 0 until someone actually uses the worksheet?

Code:
=IFERROR(IF(E204:E225<>"",SUM(E204:E225-OFFSET(E204:E225,-1,1))+MAX(F204:F225)),0)
 
Upvote 0

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Re: Need help with IF statement

Forgot to mention that yes, I did enter the formula as an array formula, using the CTRL+SHIFT+ENTER.

Code:
{=IFERROR(IF(E204:E225<>"",SUM(E204:E225-OFFSET(E204:E225,-1,1))+MAX(F204:F225)),0)}
 
Upvote 0
Re: Need help with IF statement

I didn't ELSE the IF statement, which you don't actually need, so:
{=SUM(E204:E226-OFFSET(E204:E226,-1,1))+MAX(F204:F226)}
 
Upvote 0
Re: Need help with IF statement

Perfect! Thank you so much!

Cheers!
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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