IF statement

leopardhawk

Active Member
Joined
May 31, 2007
Messages
349
Office Version
2016
Platform
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.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">201</td><td style="border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;font-style: italic;;">Waypoints</td><td style="font-weight: bold;text-align: center;font-style: italic;;"></td><td style="font-weight: bold;text-align: center;font-style: italic;;">Odometer</td><td style="font-weight: bold;text-align: center;font-style: italic;;"></td><td style="font-weight: bold;text-align: center;font-style: italic;;">Driving Time</td><td style="font-weight: bold;text-align: center;font-style: italic;;"></td><td style="font-weight: bold;text-align: center;font-style: italic;;"></td><td style="font-weight: bold;text-align: right;border-right: 1px solid black;font-style: italic;color: #FF0000;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">202</td><td style="font-weight: bold;border-left: 1px solid black;font-style: italic;;">Travel Date</td><td style="font-weight: bold;font-style: italic;;">Start</td><td style="font-weight: bold;font-style: italic;;">Finish</td><td style="font-weight: bold;font-style: italic;;">Start</td><td style="font-weight: bold;font-style: italic;;">Finish</td><td style="font-weight: bold;font-style: italic;;">Start</td><td style="font-weight: bold;text-align: right;font-style: italic;;"> Finish </td><td style="font-weight: bold;text-align: right;font-style: italic;;">Drive Hours</td><td style="font-weight: bold;text-align: right;border-right: 1px solid black;font-style: italic;;"> Mileage </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">203</td><td style="border-left: 1px solid black;;">2019-01-01</td><td style=";">Home</td><td style=";">Raymond, WA</td><td style="text-align: right;;">20135</td><td style="text-align: right;;">20566</td><td style="text-align: right;;">07:00</td><td style="text-align: right;;">13:35</td><td style="text-align: right;;">6.6</td><td style="text-align: right;border-right: 1px solid black;;">268</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">204</td><td style="border-left: 1px solid black;;">2019-01-02</td><td style=";">Raymond, WA</td><td style=";">Florence, OR</td><td style="text-align: right;;">20598</td><td style="text-align: right;;">20970</td><td style="text-align: right;;">07:12</td><td style="text-align: right;;">12:55</td><td style="text-align: right;;">5.7</td><td style="text-align: right;border-right: 1px solid black;;">231</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">205</td><td style="border-left: 1px solid black;;">2019-01-03</td><td style=";">Florence, OR</td><td style=";">Crescent City, CA</td><td style="text-align: right;;">21016</td><td style="text-align: right;;">21300</td><td style="text-align: right;;">08:30</td><td style="text-align: right;;">12:00</td><td style="text-align: right;;">3.5</td><td style="text-align: right;border-right: 1px solid black;;">176</td></tr></tbody></table><p style="width:3.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">2020</p><br /><br />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

Well-known Member
Joined
May 8, 2018
Messages
655
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:

Toadstool

Active Member
Joined
Mar 5, 2018
Messages
296
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:

kweaver

Well-known Member
Joined
May 8, 2018
Messages
655
Re: Need help with IF statement

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">201</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">201</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Waypoints</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Odometer</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Driving Time</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">202</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">202</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Travel Date</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Start</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Finish</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Start</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Finish</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Start</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Finish</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Drive Hours</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Mileage</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1878</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">203</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">203</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1/1/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Home</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Raymond, WA</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20135</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20566</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7:00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">13:35</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">6.6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">268</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">204</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">204</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1/2/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Raymond, WA</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Florence, OR</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20598</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20970</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7:12</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">12:55</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">5.7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">231</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">32</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">205</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">205</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1/3/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Florence, OR</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Crescent City, CA</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">21016</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">21300</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8:30</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">12:00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3.5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">176</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">46</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">206</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Crescent City, CA</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Somewhere, NV</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">22600</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">25000</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1300</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">207</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Somewhere, NV</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Another Place, TX</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">25500</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">26500</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">500</td></tr></tbody></table><p style="width:9.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">PeopleCities</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K202</th><td style="text-align:left">=SUM(<font color="Blue">K203:K1000</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K204</th><td style="text-align:left">=E204-F203</td></tr></tbody></table></td></tr></table><br />
 

leopardhawk

Active Member
Joined
May 31, 2007
Messages
349
Office Version
2016
Platform
Windows
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

Active Member
Joined
Mar 5, 2018
Messages
296
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

Active Member
Joined
May 31, 2007
Messages
349
Office Version
2016
Platform
Windows
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

Board Regular
Joined
Feb 27, 2018
Messages
122
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

Active Member
Joined
Mar 5, 2018
Messages
296
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>
 

leopardhawk

Active Member
Joined
May 31, 2007
Messages
349
Office Version
2016
Platform
Windows
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
 

Forum statistics

Threads
1,077,623
Messages
5,335,293
Members
399,011
Latest member
HenryK97

Some videos you may like

This Week's Hot Topics

Top