# Can someone explain how this Excel formula works?



## hdavis (Feb 14, 2012)

Leo G said:


> So if I enter in the data miles
> 
> 3/4/14.... 5000.... 5100
> 3/4/14.... 5110.... 5200
> ...


Somebody stole your truck and drove it 10 miles....


----------



## hdavis (Feb 14, 2012)

FWIW, I do this completely differently. I just do the data entry like this:

Date Odometer Category (calculated mileage)
5000
3/4/14 5100 b 100
3/4/14 5110 p 10
3/4/14 5200 b 90


Then I use SUMIF using the category and calculated mileage fields to total business and personal separately.

I also do this for expense categories, so I can enter everything paid out as it's paid, and automatically get summaries of the different categories.


----------



## hdavis (Feb 14, 2012)

hdavis said:


> Somebody stole your truck and drove it 10 miles....


Funny I missed what you were doing at least TWICE.:jester:


----------



## Tinstaafl (Jan 6, 2008)

hdavis said:


> I just do the data entry like this:


Nothing wrong with that, but it requires you to actually enter the personal miles rather than just calculating them automatically. You're not as lazy as Leo and me.


----------



## Leo G (May 12, 2005)

I already have that solved. Use the one I made.


----------



## Tinstaafl (Jan 6, 2008)

Leo G said:


> I already have that solved.


:no:


----------



## hdavis (Feb 14, 2012)

Tinstaafl said:


> Nothing wrong with that, but it requires you to actually enter the personal miles rather than just calculating them automatically. You're not as lazy as Leo and me.


Look again, that's automatically calculated. Unless you're referring to the odometer reading.

I any event, for Leo's example, I'd enter 3 odometer readings, while he enters 4. The initial reading I put in is trivial over the course of a year.

Proof I'm lazier than both of you put together.:laughing:


----------



## Leo G (May 12, 2005)

Wow, that would be one hell of a monstrosity of a formula.


----------



## Leo G (May 12, 2005)

hdavis said:


> Look again, that's automatically calculated. Unless you're referring to the odometer reading.
> 
> I any event, for Leo's example, I'd enter 3 odometer readings, while he enters 4. The initial reading I put in is trivial over the course of a year.
> 
> Proof I'm lazier than both of you put together.:laughing:


That only works if you have a dedicated vehicle for work with no personal driving.


----------



## Tinstaafl (Jan 6, 2008)

hdavis said:


> Look again, that's automatically calculated. Unless you're referring to the odometer reading.


That's what I meant. It's actually probably (without actually sitting here and analyzing it) the same amount of work either way.


----------



## Philament (Dec 9, 2014)

If you want to make it generic and self totaling, you could do something like this:

A B C D
Date Start Stop Total

Where the toatal in column D =C-B

Then in cell E2, you could have the Period Start as the first numerical value in column B using the formula =INDEX(B:B,MATCH(1,INDEX(ISNUMBER(B:B)+0,0),0))

Period Stop in a cell F2 would be the last numerical value in column C using the formula =LOOKUP(9.99999999999999E+307,C:C)

In a cell G2, Total miles travelled in Period = F2-E2
In cell H2 total business miles would be =SUM(D: D)
In cell I2 Total personal miles would be = G2-H2 

This would update every time you added a new entry automagically


----------



## hdavis (Feb 14, 2012)

Leo G said:


> That only works if you have a dedicated vehicle for work with no personal driving.



Nope, it only sums it if the category notations matches whatever your conditional is.


----------



## Leo G (May 12, 2005)

Philament said:


> If you want to make it generic and self totaling, you could do something like this:
> 
> A B C D
> Date Start Stop Total
> ...



Apparently you don't realize that we don't have a clue what we're doing.:laughing:

Will this calculate the spaces between the mileage that would be considered personal mileage?


----------



## Leo G (May 12, 2005)

=INDEX(B:B,MATCH(1,INDEX(ISNUMBER(B:B)+0,0),0))

The MATCH function isn't picking the number up in the B2 cell. I changed the 1 to a Zero and it picked up the word Start, of course that's text and not a value so it doesn't help us.


----------



## Philament (Dec 9, 2014)

Leo G said:


> Apparently you don't realize that we don't have a clue what we're doing.:laughing:
> 
> Will this calculate the spaces between the mileage that would be considered personal mileage?


Sorry, I tried to spell it out as good as I could. 
I just mocked it up and attached it. Try it out and let me know. 

Here's a screen shot using some example numbers










Let me know what part you would like to know more about and I'll do my best to explain it in plain english.

I should note that I'm assuming you're only entering business travel and everything not entered is personal


----------



## Leo G (May 12, 2005)

My version must be to old. Not working.


----------



## Philament (Dec 9, 2014)

Leo G said:


> My version must be to old. Not working.


What version are you using? I'll try to export it. I think I saved that as "Excel 97-2003" format


----------



## Leo G (May 12, 2005)

That's what I'm running 2003


----------



## Philament (Dec 9, 2014)

Leo G said:


> That's what I'm running 2003


Just fired up my old laptop with excel 2002 and it worked fine. Maybe the forum does something funny to the excel files. PM me your email and I'll send it to you.


----------



## Leo G (May 12, 2005)

It's unlikely that it messed it up. But I PMed my email addy anyway.

I tried putting the formulas in manually by copying them off your post and got the same results. Had to clean up some extra spaces.


----------



## Philament (Dec 9, 2014)

Leo G said:


> It's unlikely that it messed it up. But I PMed my email addy anyway.
> 
> I tried putting the formulas in manually by copying them off your post and got the same results. Had to clean up some extra spaces.


Those particular formulas are very column specific because they use column notation instead of range notation. 

E.g. B:B means all of column B, whereas a lot of your formulas used B1:B6554, which is a subset of the column. It just makes things more generic, but also means that you should only have numeric values in a given column or it will pick up the wrong thing. 

emailed you the file.


----------



## Leo G (May 12, 2005)

Well, going on that information I changed B:B to B3:B400 and now it works.


----------



## Philament (Dec 9, 2014)

I just did some checks in 2003. Seems like the ISNUMBER function isn't returning an array properly when you give it column notation in excel 2003, but it does in 2010. 

The fix is to use the range of cells in column from B:B to B1:B65535
=INDEX(B:B,MATCH(1,INDEX(ISNUMBER(B1:B65535)+0,0),0))

Attached new file with changes. Hopefully that will work for those using 2003


----------



## Leo G (May 12, 2005)

I already did those changes and it worked. I did however change the column notation B:B to a range in the INDEX function also. Still worked.

Just put the notation to B:B in the INDEX function and it doesn't work. Seems to have to have a range. I used B4:B400


----------



## Xtrememtnbiker (Jun 9, 2013)

So... Where does one figure out what the heck you guys are talking about? 

I know how to do SUM and ROUNDUP. That might be it. Don't run the business but might be able to help Dad speed stuff up.


----------



## Leo G (May 12, 2005)

Guess the range needs to start at B1 where I had it at B4, works when I do that.


----------



## Philament (Dec 9, 2014)

Xtrememtnbiker said:


> So... Where does one figure out what the heck you guys are talking about?
> 
> I know how to do SUM and ROUNDUP. That might be it. Don't run the business but might be able to help Dad speed stuff up.


Honestly...google. lol. I don't memorize any of that stuff. 
Knowing what you want to achieve is the only difficult part. In this case I knew that the last entry in a column would be the final odometer reading, so I just googled "find last number in column excel"...copied the first formula I found.


----------

