I have the same problem. I reference cells in another sheet using data analysis, hit ok, and it comes up with a contagious cell reference error. When I look at the cells I referenced, it's changed completely to "Data!R2C8:R62C12"
Have you fixed your problem? If so, how?
@DougCooley
Looking for ways to know how to input a range that contains non-numeric data in Descriptive Statistics in Excel? Sometimes, when we try to input data in Descriptive Statistics it shows an error that describes that the data is non-numeric. We can resolve this error by going through some quick steps. Here, you will find 6 ways to Input a range that contains non-numeric data in Descriptive Statistics in Excel.
What Is Descriptive Statistics?
Descriptive Statistics is used to summarize a given data found from any study. It can provide basic information and the internal relationship between the variables in a dataset.
Why Does “Input Range Contains Non-Numeric Data” Error Appear in Descriptive Statistics?
Descriptive Statistics can work only on numeric values. Therefore, when non-numeric data is used for Descriptive Statistics, it shows an error that instructs us to input a range that contains non-numeric data.
Descriptive Statistics – Input Range Contains Non-Numeric Data: 6 Possible Solutions
Here, we have a dataset containing the Name and Age of some people. However, you can see that the values in Cell range C5:C12 are in Text Format.
As a result, when we input this cell range in descriptive Statistics it shows an error as shown below.
Now, we will show you how to input a range that contains non-numeric data in Descriptive Statistics in Excel using this dataset.
1. Using Number Format
In the first method, we will show you how to input a range that contains non-numeric data in Descriptive Statistics using Number Format.
Sometimes, you may find that the Data Analysis Command is not added in your Excel Workbook. Now, we will show you how to add the Data Analysis Command and input a non-numeric data range in Descriptive Statistics.
Go through the steps to do it on your own.
Step-01: Adding Data Analysis Command in Excel Workbook
We can do complex statistical and engineering analysis of data by using Data Analysis Command in Excel. To add Data Analysis Command in Excel workbook, we have to load Analysis ToolPak. Follow the steps given below to add Data analysis Command in your Excel workbook.
- Firstly, click on the File tab.
- Then, click on the Options tab.
- Now, the Excel Options box will open.
- After that, go to the Add-ins tab >> click on Analysis ToolPak.
- Then, click on Go.
- Next, the Add-ins box will appear.
- After that, turn on the Analysis ToolPak option.
- Finally, click on OK.
Step-02: Using Number Format to Input Non-Numeric Data Range
Now, we will show you how to change the Text Format to Number Format of the Cell range to input non-numeric data range in Descriptive Statistics.
Go through the steps given below to do it on your own.
- In the beginning, select Cell range C5:C12.
- After that, go to the Home tab >> click on Number Format.
- Then, select Number.
- Now, you will see that Cell range C5:C12 is in Number Format.
- Next, go to the Data tab >> click on Data Analysis.
- Then, the Data Analysis box will appear.
- Now, select Descriptive Statistics.
- After that, click on OK.
- Now, the Descriptive Statistics box will open.
- Then, select Cell range C4:C12 in the Input Range box.
- After that, turn on Labels in first row option.
- Next, input Cell G4 as Output Range.
- Afterward, select any Statistical option. Here, we will select Summary statistics.
- Then, click on OK.
- Finally, you will get your desired Descriptive Statistics analysis of the given data using Number Format.
2. Applying Paste Special Feature
Now, we will show you how to remove the input range that contains non-numeric data errors in Descriptive Statistics by applying the Paste Special Feature.
Follow the steps given below to do it on your own
Steps:
- Firstly, insert 1 in any Cell of your Excel worksheet. Here, we will insert 1 in Cell C14.
- Then, select Cell C14 and click on the Copy button.
- After that, select Cell range C5:C12 and Right-click on it.
- Next, click on Paste Special >> select Paste Special.
- Now, the Paste Special box will appear.
- Then, select All from Paste options.
- Next, select Multiply from Operation options.
- After that, click on OK.
- Now, we will see that Cell range C5:C12 is in General Format.
- Now, follow the same steps shown in Method 1 to insert Descriptive Statistical analysis of the dataset.
- Finally, you will get your desired Descriptive Statistics of the given data by applying Paste Special Feature.
Read More: How to Create a Descriptive Statistics Table in Excel
3. Using Error Check to See Non-Numeric Data
We can also use Error Check to see the non-numeric data and convert it into Number to input non-numeric data range in Descriptive Statistics.
Go through the steps given below to do it on your own.
Steps:
- In the beginning, select Cell range C5:C12.
- Then, click on the Error box.
- Next, click on Convert to Number.
- Now, we will see that Cell range C5:C12 is in General Format.
- Now, follow the same steps shown in Method 1 to insert Descriptive Statistical analysis of the dataset.
- Finally, using the Error box, you will get your desired Descriptive Statistics of the given data.
4. Convert Text to Numeric Data with Text to Columns Feature
In the fourth method, we will show you how to convert Text to Numeric data with Text to Column Feature to input non-numeric data. Here, we have an additional column named Age in Number to input the Numerical data.
Follow the steps given below to do it on your own dataset.
Steps:
- Firstly, select Cell range C5:C12.
- Then, go to the Data tab >> click on Data Tools >> select Text to Columns.
- Now, the Convert Text to Columns Wizard box will appear.
- Next, select Delimited.
- Then, click on Next.
- Again, click on Next.
- After that, select General as Column data format.
- Then, input Cell D5 as Destination.
- Next, click on Finish.
- Now, we will see that Cell range C5:C12 is in General Format.
- Then, follow the steps shown in Method 1 to open the Descriptive Statistics box.
- Now, select Cell range D4:D12 as Input Range.
- After that, turn on Labels In first row option.
- Next, input Cell G4 as Output Range.
- Afterward, select any Statistical option. Here, we will select Summary statistics.
- Then, click on OK.
- Finally, you will get your desired Descriptive Statistics of the given data using the Text to Column Feature.
Read More: How to Get Summary Statistics in Excel
5. Use of Value Function
Next, we will show you how to input a range that contains non-numeric data in Descriptive Statistics in Excel using the Value Function. The Value Function is used to convert a numeric Text string into a Number.
Go through the steps given below to do it on your own.
Steps:
- Firstly, select Cell D5.
- Then, insert the following formula.
Here, in the Value function, we inserted Cell C5 as text.
- Now, press ENTER.
- After that, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Then, you will get all the values of Age in General Format.
- Now, go through the steps shown in Method 3 to insert Descriptive Statistical analysis of the dataset.
- Finally, you will get your desired Descriptive Statistics of the given data using the Value Function.
6. Using Mathematical Operation
In the final method, we will show you how to input a range that contains non-numeric data in Descriptive Statistics in Excel using Mathematical Operation.
Follow the steps given below to do it on your own dataset.
Steps:
- Firstly, select Cell D5.
- Then, insert the following formula.
Here, we multiplied Cell C5 by 1 to convert the Text value into a Number.
- Next, press ENTER.
- After that, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Now, you will get all the values of Age in General Format.
- Now, go through the steps shown in Method 3 to insert Descriptive Statistical analysis of the dataset.
- Finally, you will get your desired Descriptive Statistics of the given data using the Mathematical Operation.
Practice Section
In this section, we are giving you the dataset to practice on your own and learn to use these methods.
Download Practice Workbook
Conclusion
So, in this article, you will find 6 ways to input a range that contains non-numeric data in Descriptive Statistics in Excel. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here. Thank you!