calculating average in cells, not including zeros

[kingy]

Hi guys

Haven't posted on here before so excuse my ignorance.

I am need of calculating the average of different cells (not in a line) that doesn't include zero values in working out the average.

I am aware of the AVERAGEIF function but I still cannot get this function to work. Please see screen clip below while I explain.

Capture.PNG

I am trying to calculate grades for senior HPE students. The problem I am having is with Liam. as you can see he has been given scores for each criteria of his badminton assessment (numbers represent grades) but he missed his exam which results in a N (or not rated) grade for each criteria which in turn returns a zero score. In this instance I am trying to work out the average of his first term Acquire grades (13 for badminton and 0 for essay).

I have used the formula =ROUND(+AVERAGEIF(C39,F39),"<>0") but I keep gettin DIV/0! as the response.

Obviously for some students they wont have zeros and some will have zeros in the first value and a number as the second.

I have set up the spreadsheet that will
- calculate separate criteria i.e. separate Acquire, separate Apply and separate Evaluate grades.
- I also intend to do this cumulative by just working out term 1 averages first (two values) then working out term 1 and 2 averages next (4 values), term 1,2 and 3 averages nest (6 values)


My question to you all is what formula can I use that will
- allow me to calculate averages of a number of values (not running cells) that may include zero values (that I don't want included in avg)
- will also round up the average to the nearest whole number


I have tried for hours to try and work this one out!!

any help would be awesome.

thanks

[sktneer]

It should be like this.....

Please Login or Register  to view this content.

[Ace_XL]

You need AVERAGEIFS since you intend to calculate separate criteria i.e. separate Acquire, separate Apply and separate Evaluate grades.

=ROUND(AVERAGEIFS(C39:H39,C37:H37,"Acquire",C39:H39,"<>0"),2)

If this does not help, please upload a sample worksheet with desired results