Import Excel Sheet Values and Average Numbers Stored as Text
Question details
Display N/A when a referenced cell is blank and average values that may include a greater-than sign.

- 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.
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.
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.
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.
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.
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.
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 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. Open the files: Open the destination workbook in WPS Spreadsheets. If the source is a separate CSV file, open it as well.
- 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. Convert the text values: In E5, enter =IFERROR(IF(TRIM(D5&"")="","",VALUE(SUBSTITUTE(D5&"",">",""))),"") and drag the fill handle down to E8.
- 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.

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.




