Function Problems

Import Excel Sheet Values and Average Numbers Stored as Text

Nimra MalikNimra Malik Nov 18, 2026 868 views

Question details

Display N/A when a referenced cell is blank and average values that may include a greater-than sign.

Import Excel Sheet Values and Average Numbers Stored as Text
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet references values from another sheet or CSV file and needs to calculate an average from entries such as >5.
Observed behavior
Blank references should display N/A, while the numeric portions of valid entries should contribute to the average.
Before you start

Check the source worksheet name and the cells you want to reference. If the source is a CSV file, open it in Excel so you can select the source cell and create the external reference.

Solution 1Recommended

Reference the source cell and average cleaned values with a helper column

Use this method to show N/A for blank references and exclude blanks or nonnumeric text from the average.

1
Reference the source cell

In the destination cell, enter =IF('DONO.csv'!G3="","N/A",'DONO.csv'!G3) if the source is a worksheet named DONO.csv in the same workbook. Replace the sheet name and cell address with your actual source.

2
Link an external CSV if needed

If DONO.csv is a separate file, open it and select its G3 cell while building the IF formula. Excel will insert an external workbook reference; use the same reference for both the test and the result.

3
Clean the values

In an empty helper cell such as E5, enter =IFERROR(IF(TRIM(D5&"")="","",VALUE(SUBSTITUTE(D5&"",">",""))),""). Drag the fill handle down through E8 to process D5:D8.

4
Calculate the average

In the result cell, enter =IFERROR(AVERAGE(E5:E8),"N/A"). The AVERAGE function ignores blank helper results, and IFERROR displays N/A if there are no valid numbers.

Reference the source cell and average cleaned values with a helper column
Meaning of greater-than values: Removing > treats an entry such as >5 as the number 5 for calculation. It does not preserve the fact that the original value was greater than 5.
Work with spreadsheet formulas

Reference and average text values in WPS Spreadsheets

Use cross-sheet references and a helper column in WPS Spreadsheets to show N/A for blank cells and average the numeric portions of text values.

  1. 1. Open the files: Open the destination workbook in WPS Spreadsheets. If the source is a separate CSV file, open it as well.
  2. 2. Create the reference: In the destination cell, enter =IF('DONO.csv'!G3="","N/A",'DONO.csv'!G3) for a worksheet named DONO.csv. For a separate CSV, select the source cell while entering the formula so WPS inserts the external reference.
  3. 3. Convert the text values: In E5, enter =IFERROR(IF(TRIM(D5&"")="","",VALUE(SUBSTITUTE(D5&"",">",""))),"") and drag the fill handle down to E8.
  4. 4. Average the results: Enter =IFERROR(AVERAGE(E5:E8),"N/A") in the result cell to average valid numbers or display N/A when none are available.
Reference cells from another worksheet or an open CSV fileClean values such as >5 before calculating an averageOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface with formula tools
microsoft office alternative - wps office

Frequently Asked Questions

Why does a blank referenced cell need an IF formula?

A direct reference can display zero when the source cell is blank. Use =IF('DONO.csv'!G3="","N/A",'DONO.csv'!G3) to display N/A instead, replacing the sheet name and cell address as needed.

Can I average values like >5 directly?

Values containing > are generally treated as text. Use SUBSTITUTE to remove the symbol and VALUE to convert the remainder to a number before averaging it.

Will blank cells or N/A affect the average?

The helper formula returns an empty string for blanks and nonnumeric text such as N/A. AVERAGE ignores those results when applied to the helper range.

How do I reference a CSV file instead of a worksheet in the same workbook?

Open the CSV file, start the formula in the destination workbook, and select the source cell in the CSV. Excel inserts an external reference containing the workbook and worksheet names.