$\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

1 Answer

$\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.

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

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy