Average percentage cells, ignoring 0% values (not in a range)

[megsterne]

Hi! Please please help! I am trying to make a template for my 2nd grade class and have spent hours, and can't figure it out! I want to create an average percentage. But when I make the average formula for every other column (for each assignment I enter in points, and then the second column calcs the percentage based on the points value/total points possible and color codes accordingly), it keeps taking the 0% into consideration. If I take out the total points possible, it still messes up the average formula. Please help if you can! The only things I have read online are about ranges of columns, not listing individual ones....Thank you!

Math	Average Points	Average Percentage	Homework 1	Homework 2	Homework 3
Total Points Possible			20	3	1
Student
Aliana	2	1%	2	10%		0%		0%
Aram	0	0%	0	0%		0%		0%
Brenda	0	0%	0	0%		0%		0%
Brianna	0	0%	0	0%		0%		0%
Chad	0	0%	0	0%		0%		0%
Diego	0	0%	0	0%		0%		0%

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[dermie_72]

I put the data in column A, so you will need to change the range to suit, but it seems to work this way,

=SUM(A1:A12)/(COUNT(A1:A12)-COUNTIF(A1:A12,0))

A1:12 is the range, so change that just make sure all of the ranges equal, and you should be ok.

 

[megsterne]

I put the data in column A, so you will need to change the range to suit, but it seems to work this way,

=SUM(A1:A12)/(COUNT(A1:A12)-COUNTIF(A1:A12,0))

A1:12 is the range, so change that just make sure all of the ranges equal, and you should be ok.

Thanks so much! So, I can't do it with leaving the values in every other cell? It has to be a range?

 

[T. Valko]

Thanks so much! So, I can't do it with leaving the values in every other cell? It has to be a range?

Can't tell what cells you want to average.

If you're willing/able there is an add-in at the below link that allows you to post screencaps of your data.

http://www.excel-jeanie-html.de/index.php?f=1

 

[dermie_72]

Thanks so much! So, I can't do it with leaving the values in every other cell? It has to be a range?

Average will ignore blank cells, but it wont ignore 0. The formula I gave will ignore blank and 0. You don't have to pick a range, you should be able to pick columns if that helps.

=SUM(A:A)/(COUNT(A:A)-COUNTIF(A:A,0))