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)
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?
1 of 1 people found this helpful
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.
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
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:
not IsEmpty( OrderedCarMileageGroup::CarID ) // does the next mileage record exist?
( OrderedCarMileageGroup::Mileage - Mileage ) / ( OrderedCarMileageGroup::Date - Date )
Hope that helps.