Descriptive Statistics – Input Range Contains Non-Numeric Data

Descriptive Statistics can work only on numeric values. 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.

In the sample dataset below, the values in Cell range C5:C12 are in Text Format.

Solutions for Descriptive Statistics - Input Range Contains Non-Numeric Data

When we input this cell range in descriptive Statistics it shows an error as shown below.

Error Box Instructing to Input Range Contains Non-Numerical Data in Descriptive Statistics

Method 1 – Using Number Format

Step 1: Adding Data Analysis Command in Excel Workbook

Click on the File tab.

Adding Data Analysis Command in Excel Workbook

Click on the Options.

The Excel Options box will open.
Go to the Add-ins tab >> click on Analysis ToolPak.
Click on Go.

The Add-ins box will appear.
Check Analysis ToolPak.
Click OK.

Step 2: Using Number Format to Input Non-Numeric Data Range

Select Cell range C5:C12.

Using Number Format to Input Non-Numeric Data Range

Go to the Home tab >> click on Number Format.

Select Number.

Cell range C5:C12 will change to Number Format.

Cell Range Converted to Number Format to Input Range Containing Non-Numerical Data

Go to the Data tab >> click on Data Analysis.

Opening Data Analysis Box to Input Non-Numerical Data Range in Descriptive Statistics

The Data Analysis box will appear.
Select Descriptive Statistics.
Click on OK.

Opening Descriptive Statistics Box to Input Non-Numerical Data Range in Descriptive Statistics

The Descriptive Statistics box will open.
Select Cell range C4:C12 in the Input Range box.
Turn on Labels in first row option.
Input Cell G4 as Output Range.
Select any Statistical option. We will select Summary statistics.
Click on OK.

You will get your desired Descriptive Statistics analysis of the given data using Number Format.

Method 2 – Applying Paste Special Feature

Steps:

Insert 1 in any Cell of your Excel worksheet. We will insert 1 in Cell C14.

Applying Paste Special Feature

Select Cell C14 and click on the Copy button.

Using Copy Button to Input Range Containing Non Numeric Data in Descriptive Statistics

Select Cell range C5:C12 and Right-click on it.

Click on Paste Special >> select Paste Special.

Opening Paste Special Box to Input Range Containing Non Numeric Data in Descriptive Statistics

The Paste Special box will appear.
Select All from Paste options.
Select Multiply from Operation options.
Click on OK.

Cell range C5:C12 turns to General Format.

Follow the steps shown in Method 1 to insert Descriptive Statistical analysis of the dataset.
You will get your desired Descriptive Statistics of the given data by applying Paste Special Feature.

Method 3 – Using Error Check to See Non-Numeric Data

Steps:

Select Cell range C5:C12.

Click on the Error box.
Click on Convert to Number.

Using Error Check to See Non-Numeric Data to Input Range Containing Non Numeric Data in Descriptive Statistics

Cell range C5:C12 is in General Format.

Follow the steps shown in Method 1 to insert Descriptive Statistical analysis of the dataset.
You will get your desired Descriptive Statistics of the given data.

Method 4 – Convert Text to Numeric Data with Text to Columns Feature

The sample dataset below has an additional column named Age in Number to input the Numerical data.

Convert Text to Numeric Data with Text to Columns Feature to Input Range Containing Non Numeric Data in Descriptive Statistics

Steps:

Select Cell range C5:C12.
Go to the Data tab >> click on Data Tools >> select Text to Columns.

Opening Text to Columns box to Input Range Containing Non Numeric Data in Descriptive Statistics

The Convert Text to Columns Wizard box will appear.
Select Delimited.
Click on Next.

Click on Next.

Select General as Column data format.
Input Cell D5 as Destination.
Click on Finish.

Cell range C5:C12 is in General Format.

Converting Text to General Format to Input Range Containing Non Numeric Data in Descriptive Statistics

Follow the steps shown in Method 1 to open the Descriptive Statistics box.
Select Cell range D4:D12 as Input Range.
Turn on Labels In first row option.
Input Cell G4 as Output Range.
Select any Statistical option. We will select Summary statistics.
Click on OK.

You will get your desired Descriptive Statistics of the given data using the Text to Column Feature.

Method 5 – Use of Value Function

Steps:

Select Cell D5.
Insert the following formula.

=VALUE(C5)

Use of Value Function to Input Range Containing Non Numeric Data in Descriptive Statistics

Press ENTER.
Drag down the Fill Handle tool to AutoFill the formula to the rest of the cells.

You will get all the values of Age in General Format.

Follow the steps shown in Method 3 to insert Descriptive Statistical analysis of the dataset.
You will get your desired Descriptive Statistics of the given data using the Value Function.

Method 6 – Using Mathematical Operation

Steps:

Select Cell D5.
Insert the following formula.

=1*C5

Using Mathematical Operation to Input Range Containing Non Numeric Data in Descriptive Statistics

Press ENTER.
Drag down the Fill Handle tool to AutoFill the formula to the rest of the cells.

You will get all the values of Age in General Format.

Follow the steps shown in Method 3 to insert Descriptive Statistical analysis of the dataset.
You will get your desired Descriptive Statistics of the given data using the Mathematical Operation.