Data>Data Analysis>Regression yields "Input Range Contains Non-Numeric data" when it doesn't

Question

I'm getting the message "Regression - Input Range Contains Non-Numeric Data" when the data format is Numeric. The initial data was opened into Excel 2019 and changed from text to numeric. I've tried F3+0 in G3 to force numeric; setup a new tab and brought F3 from the 'BadTab' into the 'NewTab' without success via =BadTab!F3

My data has headers so I've tried Headers on and off in the Regression dialog box.

What can I do to convert/correct/update this situation?

Answer 1

We need to see your (sample) file.
Why a sample file is important for troubleshooting. How to do it. - Microsoft Community

The article also contains a link to a macro that you can use to anonymize your data.

Andreas.

Answer 2

Excel Spreadsheet

Answer 3

All data in column A:E and O:S is text.

Convert the data into values as described here:

Convert numbers stored as text to numbers - Office Support

Or import the data again using Power Query as described here:
Power Query - How to import a CSV file that does not match your locale - Microsoft Community

Andreas.

Answer 4

Andreas..

Thanks for your help and input.

On my side using the same file with the RegressionAnalysis tab, I see "Number" in columns F and S of the RegressionAnalysis tab but still get the same message. All columns (F, S and T) that have data in their respective cells for rows 3-31 are "Number". Relative to the OrigTest, the columns you mention are General (=text).

So, I changed the Regression selection from F and S to F and T and got things to work without any changes either to the cells, columns, rows or the file itself. Again, each column's data is "Number" except for the Labels.

Hence, I got things to work, but I still don't know why cells classified as Number don't work in column S, probably, with the Regression function of Excel's Data Analysis dialog box.

This works:

Answer 5

I understand what you are trying to accomplish, but your undertaking is pointless, it did not work as you think.

Delete all sheets except "OrigTest", delete the columns with the formulas, convert all the text into values. After that the issue is gone.
Convert numbers stored as text to numbers - Office Support

Once again: If you import the data using Power Query, you would not run into this trouble.

Power Query - How to import a CSV file that does not match your locale - Microsoft Community

Andreas.