In a Numbers spreadsheet, up-to-date version, how do I average a column of cells, with the average appearing in a designated cell at the bottom of that column, but exclude cells in that column with zeroes? BTW, the column is a calculated value, referencing other column(s). Thank you, Experts!
iMac
If the column is B and the numbers in the column are actual numbers (not numeric text),
If by "exclude cells with zeros" you meant "exclude cells without numbers", you can use the AVERAGE function, which ignores text but will include zeros.
=AVERAGE(B)
One other thing: "<>0" means "not equal to exactly zero". If any of your formulas result in a very small number and you have formatted the cell to too few decimal places to see the entire number (so it looks like it is zero in the cell), it will be included in the average.
If the column is B and the numbers in the column are actual numbers (not numeric text),
If by "exclude cells with zeros" you meant "exclude cells without numbers", you can use the AVERAGE function, which ignores text but will include zeros.
=AVERAGE(B)
One other thing: "<>0" means "not equal to exactly zero". If any of your formulas result in a very small number and you have formatted the cell to too few decimal places to see the entire number (so it looks like it is zero in the cell), it will be included in the average.
Thank you VERY much. You're a very good Badunit. The issue was that I hadn't set a footer row. For that reason I kept getting errors saying a formula can't reference its own cell. Thanks for the other tips, too.
Paul
Paul,
You wrote: "The issue was that I hadn't set a footer row."
Header and Footer Rows are magic and indispensable in my book. Whenever I start a project I make sure I have a Footer Row. The templates all seem to have Headers, but not Footers, so I make it a habit to add them before I begin. I always find something that needs to be in a Footer cell.
Good luck with your project.
Jerry
Average a Column in Numbers
