Forum Discussion

Jake11580's avatar
Jake11580
Copper Contributor
Mar 29, 2022
Solved

AVERAGE function error

So, I have table converted from the website and it´s updating every 16h.

 

I have another sheet where I want to add average value of the defined column from the table. The formula is: =AVERAGE(Table_0[Goals GF/GP])

 

Then I get error: #DIV/0!

 

I have tried everything, no zeros, no empty cells and every cell format should be number in the table, but It still won´t work.

 

Also, for some reason, I can't increase or decrease the decimal numbers in the cells in a table, or change the format to a percentage. 

 

Can you help with this?

 

Please see attached photo of the column:

 

 

 

  • Jake11580 

    The values in your screenshot are left-aligned. This makes me suspect that they are text values, not real numbers.

    Select the range.

    Make sure that the number format is set to General or to Number, not to Text.

    On the Data tab of the ribbon, click Text to Columns, then click Finish.

     

    Alternatively, change your average formula to

    =AVERAGE(--Table_0[Goals GF/GP])

5 Replies

  • Jake11580 

    The values in your screenshot are left-aligned. This makes me suspect that they are text values, not real numbers.

    Select the range.

    Make sure that the number format is set to General or to Number, not to Text.

    On the Data tab of the ribbon, click Text to Columns, then click Finish.

     

    Alternatively, change your average formula to

    =AVERAGE(--Table_0[Goals GF/GP])

    • Jake11580's avatar
      Jake11580
      Copper Contributor

      HansVogelaar 

       

      Now I notice that when the table updates, all the table settings go to default and the Average function will not working anymore. 

      Is it possible to auto update the values without changing the table settings?

       

      Thank you!

    • Jake11580's avatar
      Jake11580
      Copper Contributor
      Thank you!

      The key was that Text to Columns. I just tried to set the number format from the Home tab only.
  • Jake11580 

    Your numbers are formatted as text and therefore you get the #DIV/0 error.

     

    =NUMBERVALUE(Tabelle1[@[Goals GF/GP]])

    In the attached example i entered the above formula in cell F2 and then the formula works in my sheet. 

Resources