Forum Discussion

Aaron Hildebrandt's avatar
Aaron Hildebrandt
Copper Contributor
Apr 06, 2018

Formula for Calculating Cumulative Mileage

I need help with a formula... Attached is my work.

I have a spreadsheet that tracks mileage for our fleet. At the end of every month, we get odometer readings from our vehicles. The thing is, sometimes one or more of our vehicles are in maintenance for the month and does not get driven, so there's a blank cell, and it throws off the formula, so it has to be manually calculated. 

 

 

I tried using this formula in row 2 =(C2-B2)+(D2-C2)+(E2-D2)+(F2-E2)+(G2-F2)+(H2-G2)+(I2-H2)+(J2-I2)+(K2-J2)+(L2-K2)+(M2-L2)+(N2-M2)

 

I should be getting 341, however, since the vehicle can be in maintenance, or the year is not finished, I get -47819. 

 

Is there a better formula that can account for the months where the vehicle is in maintenance and no mileage is gained but still acquire the total mileage for the year? 

Any help would be much appreciated!

    • Aaron Hildebrandt's avatar
      Aaron Hildebrandt
      Copper Contributor

      Thank you so much for the quick response.

       

      If you would like, could you explain to me a little bit about the formula itself?

       

      I would like to learn from it.

       

      Once again thank you.

      • Gourab Dasgupta's avatar
        Gourab Dasgupta
        Iron Contributor

        you don't need me to learn just write down the formula in a paper and read it and try to analyze it you will get the idea. bye bye  

Resources