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

  • Thread starter Thread starter jose
  • Start date Start date
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
 
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)
 
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
 
The -- is used to convert Trues/falses to 1's/0's. =sumproduct() likes to work
with numbers.

-true = -1
--true = --1 = +1
 
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)
 
Back
Top