Aggregation Functions Return Error Despite Number Format

[Ukrslan]

I’m trying to use functions like =AVERAGE(K2:K5) in Excel on a column that looks like it contains valid numbers, and the cell format is already set to Number. However, I get a #DIV/0! or sometimes #VALUE! error.


Any tips on how to force Excel to treat these as actual numbers so that AVERAGE, SUM, etc. work correctly?


Thanks!

 

[AlanSidman]

It is difficult to determine the exact issue from a picture, however, it appears that because the "numbers" are oriented the the left margin of each cell, these are probably text and not numbers. I suggest that you bring the table/range into Power Query (Get and Transform Data) found on the data tab of the ribbon. While in PQ, you can then convert all the "numbers" to decimals using the User Interface.
1. Highlight the columns in question
2. In the upper left corner of the header, click on the icon.
3. Select Change Type and select decimal
4. Select Close and Load-->Indicate where you wish the updated data to go.

If you need further help, then upload a sample workbook and not a picture as we cannot manipulate data in a picture.

 

[Debaser]

Or copy a blank cell, select the number cells, then use Paste Special - Values - Add.

 

[p45cal]

Try the likes of
=AVERAGE(VALUE(K2:K5))