Creating (What I Think is..) A Price List

J

jose

Hello All,

Just wondering if this is possible. I am trying to analyze a set of
car data to find out the approx. retail price of a car (similar to a
book value). For example, I have a list of roughly 50 cars with the
year, and number of miles on each one. From this I would like to be
able to enter in a year and milage, and have the spreadsheet produce
an approx retail value. Can excel do this? ..if so any starting point
would be greatly appreciated.

I do understand about depreciation, and that different makes of cars
are worth different amounts of money and maintain resale values
differently. This is just a simple spreadsheet.

Thanks in advance for any help,

Jose
 
B

Bob Phillips

Assuming that your car details are on Sheet1, Year in A2:An, mileage in
B2:Bn, value on C2:Cn..

On Sheet 2, A1 will hold the Year, A2 minimum mileage, A3 maximum mileage.
Then in C1 enter this formula

=SUMPRODUCT(--(Sheet1!A2:A100=A1),--(Sheet1!B2:B100>=A2),--(Sheet1!B2:B100<=
A3),Sheet1!C2:C100)


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
J

jose

Hi RP,

Thanks for the response. I tried your worksheet formulas, but it
seems to just sum up all of the values for the given parameters. What
I'm looking for is more of a book value of what the car is worth based
on how old it is and how many miles are on it. As well, I'm not
really experienced in Wrksheet formulas so if you could tell me what
the "--" are for that would be great.

Thanks Again

Jose
 
D

Dave Peterson

The -- is used to convert Trues/falses to 1's/0's. =sumproduct() likes to work
with numbers.

-true = -1
--true = --1 = +1
 
B

Bob Phillips

Surely that is because nowhere have you mentioned the car, type. model, or
whatever. You said you want value of cars of a year and a certain mileage.
That is what you got.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Top