For example on the left-hand side you can see Customer ID looks similar to numbers.

Why is my average formula not working in excel. I checked the column. You accidentally enabled Show Formulas in Excel. IF E2W H20IF I2I20 0 will convert a text-formatted number to an actual number then your AVERAGE function should work.

Change formula to this. When Excels AVERAGE function doesnt give true results try AVERAGEA. The problem is most likely caused by accidentally changing the calculation setting from Automatic to Manual.

Share the link from where I can download your Excel workbook. Numbers formatted as a text is one of the common reason for formulas not working in excel. Excel formulas are not updating.

To check what calculation mode Excel is in go to the Formulas tab and click on Calculation Options. If AVERAGE or SUM refer to cells that contain VALUE. 6 Main Reasons for Excel Formula Not Working with Solution Reason 1 Cells Formatted as Text.

In order to overlook the error values well construct a formula that ignores the errors in the reference range while calculating the average with the remaining normal values. To check if Show Formulas is turned on visit the Formula tab in the ribbon and check the Show Formulas button. You can see from my screenshot that I had selected multiple cells but it is only showing me the Count though I had asked for Average and Sum as well.

However the formulas still wont start working until you force Excel to reconsider the content. The alternative is to use the SUMPRODUCT function. If youre still having an issue with drag-to-fill make sure your advanced options File Options Advanced have Enable fill handle checked.