I made a scattergraph (Car price Y; Km X). It is ok for comparison at say a given level of Km, but as prices are scaled from $4000 to $7500 and 0 to 120 kliometers it is a little artificial in so far as an extra $1000 might get a car that has done 40,000 fewer km .

I'm wondering what statistical/ mathematical measures I might use to squeeze more out of the data.

Hope that's not confusing, thanks
John

John -

One suggestion: Explain the variation in price using more explanatory
variables (i.e., other characteristics of the cars). Use multiple
regression.

- Mike
www.MikeMiddleton.com


"JohnH" wrote in message ...

I made a scattergraph (Car price Y; Km X). It is ok for comparison at say a
given level of Km, but as prices are scaled from $4000 to $7500 and 0 to
120 kliometers it is a little artificial in so far as an extra $1000 might
get a car that has done 40,000 fewer km .

I'm wondering what statistical/ mathematical measures I might use to
squeeze more out of the data.

Hope that's not confusing, thanks
John

On Sat, 30 Jun 2007, in microsoft.public.excel.charting,
JohnH said:

I made a scattergraph (Car price Y; Km X). It is ok for comparison at
say a given level of Km, but as prices*are scaled*from* $4000 to $7500*
and 0 to 120 kliometers it is a little artificial in so far as an extra
$1000 might get a car that has done 40,000 fewer km .
*
*I'm wondering what statistical/ mathematical measures I might use to
squeeze more out of the data.


You could use the "Exponential" trendline type in the chart to calculate
a dollars per mile marginal depreciation rate. The trendline equation
that is displayed looks like this

y = e^[some number]x

The number (which we'll call K) will probably be negative if the cars
are getting cheaper the more miles they have. The equation is another
way of saying

cost = exp(K times mileage)

or

ln(cost) = K times mileage

Copy the number K, including the negative sign (don't forget to get lots
of digits! the more the better) and paste it back into a cell in your
spreadsheet. K is the average marginal depreciation, in dollars per
mile, of the cars *by cost*.

dollars cheaper/extra mile = K times cost

Since that's a little awkward (you'd rather have the depreciation for a
car of miles travelled), you want to convert the cost into mileage using
the same equation as above

dollars cheaper/extra mile = K times exp(K times mileage)

It's okay for K to appear twice in that equation, that's not a mistake.
Now you should be able to look at a car of 100,000 miles, and guess how
many dollars value it would lose by running another 10,000 miles. Of
course, different models of car would have different depreciation rates.
If your database is big enough, you might like to calculate a different
depreciation for every model of car.

If you don't want to use the trendline, you can use LINEST() to get K
from a column of LN(cost) against a column of mileage. If you're not
sure you got the right number, you can check to see if LINEST and the
trendline are giving the same answers.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

Thanks for the suggestions, I'll give it a go. I also thought of guestimating the life of a car, subtracting km travelled and dividing by the price to estimate the km's bought for each dollar??
John
"JohnH" wrote in message ...
I made a scattergraph (Car price Y; Km X). It is ok for comparison at say a given level of Km, but as prices are scaled from $4000 to $7500 and 0 to 120 kliometers it is a little artificial in so far as an extra $1000 might get a car that has done 40,000 fewer km .

I'm wondering what statistical/ mathematical measures I might use to squeeze more out of the data.

Hope that's not confusing, thanks
John

On Sun, 1 Jul 2007, in microsoft.public.excel.charting,
JohnH said:

Thanks for the suggestions, I'll give it a go.

Be aware that I made a mistake and forgot a second variable, the
constant that occurs when mileage is zero. So there are two variables to
find.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.