Number formatting error

Hi Matthew,

Thank you for the forum question,

You’re on the right track—this is a very common Excel issue. What you’re seeing is almost certainly caused by numbers being stored as text, which is why SUM returns 0.
Your workaround:
=IFERROR(VALUE(A1:C6), A1:C6)
does work, but it’s not the most efficient or scalable solution (especially across larger ranges or tables). Here are better approaches depending on your setup.
________________________________________
Root cause
The imported/transposed data (likely from Power Query) is coming through as:
• Text values that look like numbers (e.g. "1" instead of 1)
• Plus literal text like "n/a"
Excel math functions ignore text → result = 0
________________________________________
Best solutions
Option 1: Fix it in Power Query (recommended)
Since you’re already using Power Query, this is the cleanest and most efficient solution.
In Power Query:
1. Select the assessor columns
2. Set data type to:
o Whole Number
3. Replace "n/a" with:
o null (so Excel treats it as blank)
Why this works:
• Numbers become true numeric values
• null behaves properly in calculations (ignored in sums, but countable if needed)
If you must keep "n/a" visible, you can convert to numbers for calculations and then re-display later in Excel
________________________________________
🔹 Option 2: Use a helper formula (better than your current one)
Instead of applying VALUE to an array, process each cell like this:
=IF(A1="n/a","n/a",--A1)
or safer:
=IFERROR(--A1, A1)
Why better than your formula:
• --A1 (double unary) is faster and more commonly used than VALUE
• Works cleanly row-by-row
• Keeps "n/a" intact
________________________________________
🔹 Option 3: Convert in place (fast manual fix)
If you don’t want formulas:
Method A: Multiply trick
1. Enter 1 in an empty cell
2. Copy it
3. Select your data range
4. Paste Special → Multiply
Converts text numbers to real numbers instantly
Will error on "n/a" unless filtered/handled first
________________________________________
Method B: Text to Columns
1. Select the data
2. Go to Data → Text to Columns
3. Click Finish
Forces Excel to re-evaluate data types
________________________________________
🔹 Option 4: Use VALUE only where needed
If sticking with formulas, refine yours like this:
=IF(A1="n/a","n/a",VALUE(A1
________________________________________
Handling "n/a" in analysis
Since your statistical sheet expects "n/a":
• Keep "n/a" as text
• Convert only valid numbers
For counting valid entries:
=COUNT(range)
For counting all entries including "n/a":
=COUNTA(range)
________________________________________
Summary (what you should do)
Best overall approach:
• Fix data types in Power Query
• Convert "n/a" → null
If working in Excel only:
• Use:
=IFERROR(--A1, A1)
Avoid:
• Array use of VALUE() like your current formula (inefficient and harder to manage)
________________________________________
Quick diagnostic tip
To confirm the issue:
=ISNUMBER(A1)
• FALSE → your number is stored as text
________________________________________
If you want, I can help you tweak your Power Query steps so this is solved permanently at the source



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector