IF statement

[leopardhawk]

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
	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	2019-01-01	Home	Raymond, WA	20135	20566	07:00	13:35	6.6	268
204	2019-01-02	Raymond, WA	Florence, OR	20598	20970	07:12	12:55	5.7	231
205	2019-01-03	Florence, OR	Crescent City, CA	21016	21300	08:30	12:00	3.5	176
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

 

[leopardhawk]

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?

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

 

[leopardhawk]

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.

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

 

[Toadstool]

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)}

 

[leopardhawk]

Re: Need help with IF statement

Perfect! Thank you so much!

Cheers!