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

 

[kweaver]

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?

 

[Toadstool]

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


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

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

Regards,
Toadstool

 

[kweaver]

Re: Need help with IF statement

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
201	201		Waypoints		Odometer		Driving Time
202	202	Travel Date	Start	Finish	Start	Finish	Start	Finish	Drive Hours	Mileage	1878
203	203	1/1/2019	Home	Raymond, WA	20135	20566	7:00	13:35	6.6	268
204	204	1/2/2019	Raymond, WA	Florence, OR	20598	20970	7:12	12:55	5.7	231	32
205	205	1/3/2019	Florence, OR	Crescent City, CA	21016	21300	8:30	12:00	3.5	176	46
206			Crescent City, CA	Somewhere, NV	22600	25000					1300
207			Somewhere, NV	Another Place, TX	25500	26500					500
PeopleCities

Cell Formulas
Range	Formula
K202	=SUM(K203:K1000)
K204	=E204-F203

 

[leopardhawk]

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!

 

[Toadstool]

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.

 

[leopardhawk]

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!

 

[DSCfromCFA]

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

 

[Toadstool]

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
Waypoints	Odometer	Driving Time
Travel Date	Start	Finish	Start	Finish	Start	Finish	Drive Hours	Mileage
Home	Raymond, WA
Raymond, WA	Florence, OR
Florence, OR	Crescent City, CA

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]201[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]202[/TD]
[TD="align: center"][/TD]

[TD="align: center"]203[/TD]
[TD="align: center"][/TD]
[TD="align: right"]1/1/2019[/TD]

[TD="align: right"]20135[/TD]
[TD="align: right"]20566[/TD]
[TD="align: right"]7:00[/TD]
[TD="align: right"]13:35[/TD]
[TD="align: right"]6.6[/TD]
[TD="align: right"]268[/TD]

[TD="align: center"]204[/TD]
[TD="align: center"][/TD]
[TD="align: right"]1/2/2019[/TD]

[TD="align: right"]20598[/TD]
[TD="align: right"]20970[/TD]
[TD="align: right"]7:12[/TD]
[TD="align: right"]12:55[/TD]
[TD="align: right"]5.7[/TD]
[TD="align: right"]231[/TD]

[TD="align: center"]205[/TD]
[TD="align: center"][/TD]
[TD="align: right"]1/3/2019[/TD]

[TD="align: right"]21016[/TD]
[TD="align: right"]21300[/TD]
[TD="align: right"]8:30[/TD]
[TD="align: right"]12:00[/TD]
[TD="align: right"]3.5[/TD]
[TD="align: right"]176[/TD]

[TD="align: center"]206[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]227[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]78[/TD]

</tbody>

Sheet1 (2)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J227[/TH]
[TD="align: left"]{=IF(E204:E226<>"",SUM(E204:E226-OFFSET(E204:E226,-1,1))+MAX(F204:F226))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[leopardhawk]

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