Formulas to calculate average trips per month

[tshepolt]

Good day

I'm a bit stuck here on how to analyze the attached spreadsheet.

Please assist I need to able to show opening & closing Odometer (start & End) respectively per month
and calculating Per month: the average trips per day, average distance per day

And also top 30 Longest trips from (Feb21 to Oct21)

Please your assistance will be highly appreciated

[AliGW]

Administrative Note:

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[DJunqueira]

Could you exemplify what you want in your workbook?

[GeoffW283]

I created a Summary worksheet to hold the requested data.

log_book_gw.png

In C1 is 1-Jan-2021, D1 contains 1-Feb-2021, E1 contains 1-Mar-2021 etc. etc.

In C2 copied across to O2 for the opening odometer:

Please Login or Register  to view this content.

This formula makes sure to get the very first odometer log entry for the particular month.

In C3 copied across to O3 for the closing odometer the formula is almost identical (differences are highlighted)

Please Login or Register  to view this content.

Average trips per day in C4 copied across to O4:

Please Login or Register  to view this content.

Average distance per day in C5 copied across to O5:

Please Login or Register  to view this content.

For the 30 longest trips, the start date is entered by the user in B7 and the end date is entered in B8. The results are listed in order of largest first, so in B10 copied down to B39:

Please Login or Register  to view this content.

Finally C10 to C39 is the Date/Time corresponding to each of the longest distances.

Please Login or Register  to view this content.

The attached workbook implements the above formulas.

Hopefully this is close to what you are looking for. Let us know.