I searched on "average and omit zero"
NOTE THE PART AT BOTTOM ABOUT ENTERING THE ARRAY FORMULA !!!
Worked for me....should do same for you too.
The example may be easier to understand if you copy it to a blank worksheet.
Create a blank workbook or worksheet.
Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
Formula Description (Result)
=AVERAGE(IF(A2:A7<>0, A2:A7,"")) Averages the numbers in the list except those that contain zero, such as cell A6 (11.4)
*************Note: The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the cell A9. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the error #VALUE! is returned.
I am working on a spreadsheet to take an average of prices. However, they are not consecutive (a1:10). It looks more like this
This information will be input on a weekly basis so there are currently $0.00 most of the cells. When I go to average it, it takes all the cells into consideration, including the zero cell and therefore causes the average to be much lower than it should be. I've tried =IF(ISERRROR) but it doesn't want more than (4) commas. I hope this makes sense. Your help is GREATLY appreciated.