I have a spread sheet that I am taking over and it includes average mileage.
The only issue is that not all the data is the same. Some of the entries are for example 10,000 / YR while others are 500 / MON is there a way to calculate the average without having to reformat the entries that are different than the other. (the majority of the entries are /MON)
Can you post an example file?
How do you want to calculate the average? by month or by year?
Put this in J2 to tidy up your monthly km and copy down. it won't fix the example of the TOTAL in it but will tidy up most of your data
by converting it all to Monthly data
=IF(RIGHT(E2,LEN(E2)-FIND("/",E2)-1)="YR",LEFT(E2,FIND("/",E2)-1)/12,LEFT(E2,FIND("/",E2)-1)*1)
Happy with my advice? Click on the * reputation button below
Nevermind didnt see your last column
I added a little to Crooza's formula to compensate for the values with "TOTAL"
=IF(ISNUMBER(SEARCH("T", E2)), LEFT(E2, SEARCH("T", E2)-1)/ I2, IF(RIGHT(E2,LEN(E2)-FIND("/",E2)-1)="YR",LEFT(E2,FIND("/",E2)-1)/12,LEFT(E2,FIND("/",E2)-1)*1))
Thanks phbryan for the tidy up.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks