4 Replies Latest reply on Feb 20, 2012 10:55 AM by jason.delooze

# Calculating average mileage

Hi, I have table with the mileage readings for various cars taken on various dates, (not all taken on the same date). I want to work out the average mileage for each car used between each date that particular cars mileage was recorded, can anyone help with a formula that would do this for me?

• ###### 1. Re: Calculating average mileage

Let's assume you have data for Car A on Date1 with Mileage1 and on Date2 with Mileage2.  Then the average mileage (per day) for Car A over the time period Date1 to Date2 would be

(Mileage2 - Mileage1) / (Date2 - Date1)

• ###### 2. Re: Calculating average mileage

Thanks for your response Jason,  currently I have Date1 and Date2 in different records within the same field, and likewise with Mileage1 and Mileage2.  How do I find the two nearest dates for a particular car, and do the calculation on those records?

• ###### 3. Re: Calculating average mileage

su.knight@torld.co.uk wrote:

Hi, I have table with the mileage readings for various cars taken on various dates, (not all taken on the same date).  I want to work out the average mileage for each car used between each date that particular cars mileage was recorded, can anyone help with a formula that would do this for me?

Define the following summary fields:

• MinDate = Minimum of Date;

• MaxDate = Maximum of Date;

• MinMiles = Minimum of Mileage;

• MaxMiles = Maximum of Mileage;

and a calculation field =

( GetSummary ( MaxMiles ; CarID ) - GetSummary ( MinMiles ; CarID ) ) /

( GetSummary ( MaxDate ; CarID ) - GetSummary ( MinDate ; CarID ) + 1 )

Sort the records by CarID.

1 of 1 people found this helpful
• ###### 4. Re: Calculating average mileage

What is your goal here - to calculate the average mileage per day for each time period for each car - or a single average mileage for each car (taken over the entire set of mileage records for the car)?  Example:

Car1 - 1/18/2011 - 14265

Car1 - 1/25/2011 - 14538

Car1 - 2/22/2011 - 15852

Car1 - 3/14/2011 - 16207

For Car1 over the individual time periods

1/18/2011 - 1/25/2011 - 39.0 miles per day

1/25/2011 - 2/22/2011 - 46.9 miles per day

2/22/2011 - 3/14/2011 - 17.8 miles per day

or over the entire time period

1/18/2011 - 3/14/2011 - 35.3 miles per day

Of course, the implementation would depend on your database design - what Tables (entities) you have and what attributes (fields) in each table (entity).  If we assume you have a Mileage table with attributes CarID, Date, Mileage, then a self-join between a table occurrence (Mileage) of the Mileage table with another table occurrence (OrderedCarMileageGroup) of the Mileage table defined as

Mileage::CarID = OrderedCarMileageGroup::CarID

and

Mileage::Date < OrderedCarMileageGroup::Date

and sorted by OrderedCarMileageGroup::Date (ascending order)

will allow Mileage record to know which Mileage record is next (by date) Mileage record for that Car.  The Average mileage per day follows easily:

AverageMileage =

Case(

not IsEmpty( OrderedCarMileageGroup::CarID )  // does the next mileage record exist?

( OrderedCarMileageGroup::Mileage - Mileage ) / ( OrderedCarMileageGroup::Date - Date )

)

Hope that helps.