Do I Want Regression for This??

[J. Trucking]

Hello,

I am trying to construct a pricing model for a certain type of
equipment. What I have is a whole bunch of entries of sales for this
type of equipment. The entries have the year of manufacture, miles on
the equipment, and price. What I would like to do is have a user be
able to enter in a specific year and number of miles and have the
spreadsheet return the approximate price that the equipment should be
selling for. Is there a way for excel to do a type of 2 variable
regression for this. I have searched the net and have found sites on
regression but it seems to do a whole statistical analysis. Anybody
have any ideas?

Thanks in advance,

John

 

[Billy Liddel]

Hello,

I am trying to construct a pricing model for a certain type of
equipment. What I have is a whole bunch of entries of sales for this
type of equipment. The entries have the year of manufacture, miles on
the equipment, and price. What I would like to do is have a user be
able to enter in a specific year and number of miles and have the
spreadsheet return the approximate price that the equipment should be
selling for. Is there a way for excel to do a type of 2 variable
regression for this. I have searched the net and have found sites on
regression but it seems to do a whole statistical analysis. Anybody
have any ideas?

Thanks in advance,

John

Your data needs to be sorted Model ascending, miles descending as below:

Model Miles Price
Ford Fiesta 56000 7000
Ford Fiesta 24000 10000
Ford Focus 100000 6000
Ford Focus 12000 12000

set up your criteria as:
Model Miles Price
ford fiesta 20000 10000

I put these in cells E1:G1

the lookup formula in G2 is:
=VLOOKUP(E2&F2,A1:C5,3,1)

change the ranges to suit

Peter

 

[J. Trucking]

Hi Peter,

Thanks for the response. Will this give me the "suggested retail
price" if the user enters in a certain year with a certain number of
miles?

Thanks again,

John

 

[Billy Liddel]

I missed the Year. Inlcude another column for the year and add this to the
Lookup reference.

=VLOOKUP(E2&F2,A1:C5,3,1)

would be =VLOOKUP(E2&F2&g2(A1:d5,4,1) where the extra column would be for
years. You might have to sort the data to make this work.

Model, Year, Miles, Price

Sort on Model ascending, Year Ascending and price decending I think

Peter
Peter