IFTA Mileage calculation spreadsheet killing me

[dceremuga]

Ok. Try to explain this simply. I have a list of states. The trucker has to log his miles. He may go to some states, may not go to others. I need to be able to do the calculation properly if some states are left blank. Here is an example. I added GA and FL, but don't know how to write a statement that if 0 or blank, then use the next state, or continue on until it finds a number greater than zero. Look below. The formula for the calculated is simple it's next state minus the current state. B18-b17 (as an example). Now, what if 1, 2 or many states aren't used. I can't use that formula, I can't even do a simple IF. I'm really struggling on this one.

Mileage Calculator
State Mileage Calculated Miles
SC 155300 3
NC 155303 175
TN 155478 287
KY 155765 92
Ill 155857 165
MS 156022 488
Ill 156510 160
Ky 156670 95
TN 156765 291
NC 157056 172
SC 157228 3
Ft Mill 157231
Total Miles 1931

[mcmahobt]

I get what you're asking, but could you post a sample workbook so I can make sure a solution works in the context you provide?

[dceremuga]

I get what you're asking, but could you post a sample workbook so I can make sure a solution works in the context you provide?

Lets see if this attached correctly.

Thank you.

[mcmahobt]

Copy into cell C15 and drag down:

Please Login or Register  to view this content.

[dceremuga]

I'm trying nested If's. I'm not having any luck. Hope you get me pointed in the right direction, then I can add more states and move on. Thank you.

[mcmahobt]

Did you see my last post?

[dceremuga]

Copy into cell C15 and drag down:

Please Login or Register  to view this content.

I did so. It works. I added 2 more needed states, then reapplied the formula and end up with what you see attached. The mileages are now off

[dceremuga]

My bad. Fixed. Thank you, a nice elegant solution. Now to figure out what you did so I can learn from it. How do I mark this as solved?

[mcmahobt]

Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes.