Running a regression analysis with multiple Y variables

Question

I am trying to run a regression for a stock portfolio with 4 stocks whose returns I am trying to put into the (y range) and one benchmark (NYA) that I am inputting into the (x range). I am getting an error every time I enter my data saying; Regression: Input range must be a single row or column. After watching several tutorials I am sure that is not the case. I noticed when watching some of the tutorials that the arrow next to the input range has a different look to it than mine does. Maybe there is something wrong with my data analysis tool pack download? Help would be greatly appreciated. Note, I have also tried switching the x and y variables so there are multiple x variables and only 1 y variable and the error is the same.

Answer 1

Look at how the terms are used in the description at https://www.excel-easy.com/examples/regression.html .

The big question is: is there a relation[ship] between Quantity Sold (Output) and Price and Advertising (Input). In other words: can we predict Quantity Sold if we know Price and Advertising?

The Y-range is the dependent variable (singular) -- Quantity Sold. The X-range are the independent variables (one or more) -- Price and Advertising.

Presumably, you are trying to derive a multilinear relationship between the periodic rates of return of the several securities (A2:D26) and the periodic rate of return of the benchmark (E2:E26).

So the Y-range is E2:E26, and the X-range is A2:D26.

But you write: I have also tried switching the x and y variables so there are multiple x variables and only 1 y variable and the error is the same, to no avail.

I would have prototyped the solution to see what other problems might be lurking. But I am not about to manually copy 120 numbers.

It would have been nice if you had uploaded an Excel file to a file-sharing website, and posted the download URL.

Answer 2

PS.... I wrote:

I would have prototyped the solution to see what other problems might be lurking. But I am not about to manually copy 120 numbers.

It would have been nice if you had uploaded an Excel file to a file-sharing website, and posted the download URL.

Okay, just for "proof of concept", I generated some random data. Then I used Data Analysis Regression as follows:

No error. The results are:

QED. Unless and until you provide more information -- and upload an example Excel file -- I cannot help you further.

---

PPS.... The problem with the icons to the right of the fields -- up-arrows instead of spreadsheet grids -- might be a fluke.

I would simply reboot the computer before I contemplate anything more drastic, like "repair office" or the Office 365 equivalent.

Windows does get confused like that sometimes. I do not think it is the root cause of this particular problem, as you describe it.

Answer 3

ok. Thank you. Can you tell me what icon I need to click to upload the file? Everything is greyed out except the link and the create a chart function just above this message that I am typing. There is no option to upload a file.

I just tried what you suggested and am having the same issue. I am on the phone with a classmate and she has arrow icons next to the data input range like I do and is having the same error come-up. I just rebooted as well.

Answer 4

(Resubmitted with __major__ corrections)

Can you tell me what icon I need to click to upload the file? Everything is greyed out except the link and the create a chart function just above this message that I am typing. There is no option to upload a file.

That is why I said upload an Excel file to a *file-sharing website*, and posted the *download URL*.

That means: find a file-sharing website to use; then upload the Excel file to it.

I like box.net/files; others like dropbox.com. You might like onedrive.live.com because it shares the same login as this forum.

The details for uploading a file varies with each website. So does acquiring a (sharable) download URL. It is pretty straight-forward at box.net/files, IMHO.

---

I am on the phone with a classmate and she has arrow icons next to the data input range like I do and is having the same error come-up. I just rebooted as well.

The fact that someone else is having exactly the same experience __might__ lead me to suspect your computing environment, at least to some degree.

But I don't know anything about your environment. There are too many variables (shared file spaces; cloud services; etc etc etc).

So I cannot advise you further. Perhaps you need to contact the instructor.

---

OTOH, is your classmate using the __exact__ same file that you are?

Or do both your file and your classmate's file come from the same source -- perhaps a file provided by your instructor, or a shared file?

Again, it __might__ indeed be helpful if you make the file available to us.

---

If you are willing, try downloading __my__ file (click here). Ignore any error and login request. Just proceed with the download.

1. Download the file, and open it on your computer. Look at both worksheets.

Does everything look okay (except perhaps the Data Analysis Regression icons)?

2. Click Data > Data Analysis > Regression and click OK to redo the analysis. It should create a new worksheet.

Does that work?