Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
How to average a column of cells when each column will have a different amount of cells to divide by?
I know =sum(A1:A5) will total the cells. I know that lets say cell A6 is the total cell then A7 would be the average cell. So it would be =sum(A6/by whatever number of cells). One column might be like this: A1: 1 A2:1 A3:1 A4:0 A5:1. Total would be 4 and I need it to divide by 4 instead of 5 because A4 doesn't have a number I need. The next column might only have 2 cells with numbers in it. Is there a formula to only divide by the number of cells that have a number greater than 0. Thanks
I have to leave the 0's in the column
2 Answers
- ScrawnyLv 78 years agoFavorite Answer
Seeing that you have to leave the zero values in the column but not count them for the average then this will work.
Change the cell references to match your data:
=SUM(A1:A7)/(COUNT(A1:A5)-COUNTIF(A1:A5,0)) This will subtract the number of zero values from the count of values so that zeros are not considered.
Source(s): Experience - braydenLv 78 years ago
Just use the average formula. It ignores blank cells (truly blank, not zeros) and you don't have to manually put in the divide-by number.
Example: if you're trying to average A1 to A5 then in A6 put in the formula:
=average(A1:A5)
If you're trying to average B1:B3 but you want the formula to be on the same line as A6 then in B6 put
=average(B1:B5)
Etc
