0
$\begingroup$

So I'm not good with excel (computers in general) and can do some things but this one is out of my league. This is the problem:

The cost of a used car is highly correlated with the following variables:

 t= age of car              1 ≤ t ≤ 5 (years)
 V= volume of engine        1000 ≤ V ≤ 2500 (cubic centimeters)
 D= number of doors         D= 2,3,4,5
 A= accessories and style   A= 1,2,3,4,5,6 (qualitative)

Using regression analysis, the following relationship between the cost and four independent variables was found:

purchase cost= (1+1/t)*V*(D/2+A)

Plot the purchase price of the car as a function of the four variables.

I know how to input the function into excel and only use one number from each variable:

Function:
=(1+(1/B2))*C2*((D2/2)+E2
Where: B2=1, C2=1000, D2=2, E2=1
Which: A1=4000 (for the purchase cost)

What I don't know is how to make the function use multiple number combinations within those variables (i.e. how to change one variable and not the others). I've looked up "youtube" videos and numerous websites to figure this out and none of them showed me what I needed to know (or at least understood). Also, if you could make it in simple terms, that would be great. Took calculus in high school 10 years ago.. apparently it didn't stick with me too well. Any help would be greatly appreciated.

$\endgroup$
4
  • $\begingroup$ Perhaps a SuperUser question? (I don't know, mentioning it to see what community thinks.) $\endgroup$
    – apnorton
    Sep 29, 2014 at 2:30
  • $\begingroup$ @anorton I think the crux of the matter is in the directive: "Plot the purchase price of the car as a function of the four variables." How is that even possible, in Excel or otherwise? $\endgroup$
    – user147263
    Sep 29, 2014 at 2:44
  • $\begingroup$ @CareBear Didn't see that. :\ That's odd, to be sure. $\endgroup$
    – apnorton
    Sep 29, 2014 at 2:51
  • $\begingroup$ See that's what I didn't understand but like I said, totally not excel savvy. $\endgroup$
    – jess.k
    Sep 29, 2014 at 16:29

1 Answer 1

1
$\begingroup$

One cell has one output.

What you can do however is manipulate your function in order to vary with respect to each of your parameters individually:

The \$ Operator will keep a row or column reference constant. By omitting the \$ operator you can alter your cell reference and thereby reference multiple combinations as required.

You have four parameters so it will be easiest if you use four columns in your calculations, one for variations in each of your parameter.

For example, vary purchase cost with respect to varying age of car (t) the formulae

= (1 + ( 1/\$B2)) * \$C\$2 * ((\$D\$2 / 2) + \$E$2).

Will work for the following layout.

enter image description here

What has happened here is that $B2 has fixed reference to column B, with dynamic reference to the row starting at 2. All other parameters are fixed at their respective row 2 values. So the purchase cost calculated in A3 takes its age of car parameter from B3 and so on.

You can then proceed to do the same for different combinations. By different applications of absolute or variable cell references.

Note: Where you will struggle is graphing the data you aquire from this. You have four parameters, which will be difficult to interpret on one plot given that you will have to represent the fourth parameter as a colour variance or something. If I had to do it I would reduce and qualify the range of variable parameters, or use more than one plot.

$\endgroup$
1
  • $\begingroup$ Thank you so much. I definitely didn't know how to do any of this. I appreciate it greatly. $\endgroup$
    – jess.k
    Sep 29, 2014 at 16:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .