Calculating Time and Fuel Efficiency for Race Car Laps

[acapro]

Hi,

I am not sure if this question is in the right area or can be answers in this forum, but I will put it out anyway.

I want to construct a formula to answer the following question. A race car does 10 laps, each lap the fuel load (weight) reduces therefore the lap time reduces however the driver can not complete each lap exactly the same therefore the times do not reduce consistently. What I want to do is work out at how much time does each litre of fuel cost per lap and how much time is reduced for each lap worth of fuel

example

5 litres of fuel needed per lap

lap number
10 1:22.853
9 1:22.822
8 1:22:825
7 1:22:801
6 1:22:799
5 1:22.765
4 1:22.788
3 1:22:770
2 1:22:762
1 1:22:765

I will have 10 cells on a excel worksheet where I can enter the times next to the corresponding lap number then in the 11th cell the answer =time per lap/fuel 12th cell time per litre/fuel


Thanks in advance

 

[.Scott]

I though the lap times were decreasing as fuel was consumed. You show lap #1 (presumably the first lap) as faster than lap #10. Also, I believe all of you times are suppose to be in the form 9:59.999. Six are in the form 9:59:999 (two colons).
If you're It looks to me as though you're looking for a least squares best fit.

 

[acapro]

Sorry Scott, I am used to race lap numbers not general numbers, lap 10 = 10 laps worth of fuel, lap 9 = 9 laps of fuel and so on. We work backwards from a pit stop not forwards to one

All are in 9:59.999, sorry this confused you

 

[.Scott]

OK.
I guessing you don't know what a "least squares best fit" is.
------------------------

Here's another way to explain what I think you want:

You want to know (time cost per gallon) and

You assume that there is a time formula that looks like this:
Expected Lap Time = (Lap time on empty tank) + (Gallons in tank at start of lap)*(time cost per gallon)

But the actual lap time never comes out exact because of other factors. So for each lap you have:
(Random Lap Difference) = (Actual Lap Time) - (Expected Lap Time)

But those (Random Lap Difference)s should average out so that the total of all differences is low.

So the goal is to make (Expected Lap Time) fit.
The normal way to do this is to make it minimize the total of the squares of the (Random Lap Difference)s.
That would be a "Least Squares Best Fit".

While you figure out if that makes sense, I'll figure out the formula.

--------------

Sorry, I've run out of time.
You can look up "Least Squares Best Fit"
Your going to have to set up columns for the times, the gallons of fuel, the square of the gallons, and the time*gallons. Then at the bottom of each column you need to compute the total for that column.
That will give you the numbers you need to compute the slope - which will be the time that each gallon costs.
You can get the formula for the slope here:

http://hotmath.com/hotmath_help/topics/line-of-best-fit.html

 

[acapro]

Ok I just read the "least squares best fit" topic and have established that my brain power does not meet the required level for comprehending the content. however you are on the right track. I just need the equations simplified for standard level mental computing ability.

 

[FactChecker]

EXCEL has a built in functions that will make a good estimate ("least squares best fit") through the data. The EXCEL function SLOPE(A1:A10, B1:B10) in your data above will tell you how much the time improves from lap to lap. Put '=SLOPE(A1:A10, B1:B10)' in Cell A11. Since each lap uses 5 liters of fuel, put '=A11/5' in Cell A12. To get this all to work, EXCEL needs to be able to understand what 1:22.822 or 1:22:855 means. You may have to rewrite your times into a consistent format of seconds. I think the EXCEL will interpret 1:22:855 as 1 hour, 22 minutes and ?? seconds. You may need to enter your time data with 0 hours: 0:1:22.855

P.S. I don't have EXCEL at home to test this for sure, so you will have to try it out.

 

[.Scott]

Let us know how you're doing. I guessing you still need more direction.