Excluding 0s from multiple part calculation

[Gaby_222]

I am trying to calculate the average, mean, median and standard deviation of grades inputed by different TAs. There are 4 TAs and I need to be able to compare their different results.

Currently, I have a variation of this equation for every calculation: =AVERAGE(IF(Grades!E2:E65536="D",Grades!G2:G65536,"")) (this particular one calculating the average for TA 'D' and paper #1).

I need to exclude marks of 0 from all of these calculations, because they drag the numbers down. I have tried replacing the Grades!G2:G65536 section with SUMIF, AVERAGEIF and COUNTIF functions excluding 0 but none of them produce the correct answer. Any idea how to exclude 0s? I'm sure its a very simple answer.

 

[vletm]

As written in TIP: Upload a Sample File to get a quicker response

 

[Gaby_222]

As written in TIP: Upload a Sample File to get a quicker response

Thanks, I uploaded one!

 

[Khalid NGO]

Hi,

I am not sure about the result, I have just added a condition to exclude zeros, please check these:

1)
=AVERAGEIFS(Grades!G2:G65536,Grades!E2:E65536,"D",Grades!G2:G65536,"<>0")

2)
=STDEV.S(IF(Grades!E2:E65535="D",IF(Grades!G2:G65535>0,Grades!G2:G65535,"")))

3)
=MODE.SNGL(IF(Grades!E2:E65535="D",IF(Grades!G2:G65535>0,Grades!G2:G65535,"")))

4)
=MEDIAN(IF(Grades!E2:E65535="D",IF(Grades!G2:G65535>0,Grades!G2:G65535,"")))

All formulas are {array} except the first one.

Regards,

 

[Gaby_222]

Hi,

I am not sure about the result, I have just added a condition to exclude zeros, please check these:

1)
=AVERAGEIFS(Grades!G2:G65536,Grades!E2:E65536,"D",Grades!G2:G65536,"<>0")

2)
=STDEV.S(IF(Grades!E2:E65535="D",IF(Grades!G2:G65535>0,Grades!G2:G65535,"")))

3)
=MODE.SNGL(IF(Grades!E2:E65535="D",IF(Grades!G2:G65535>0,Grades!G2:G65535,"")))

4)
=MEDIAN(IF(Grades!E2:E65535="D",IF(Grades!G2:G65535>0,Grades!G2:G65535,"")))

All formulas are {array} except the first one.

Regards,

They worked thank you so much!

 

[Khalid NGO]

Thanks for the feedback dear.
Keep visiting.

Regards,

 

[Nebu]

Hi:

You can use the following non-array formula as well
=STDEV.S(IF(MMULT((Grades!E2:E293="D")*(Grades!G2:G293>0),1)>0,Grades!G2:G293))

=MODE.SNGL(IF(MMULT((Grades!E2:E293="D")*(Grades!G2:G293>0),1)>0,Grades!G2:G293))

=MEDIAN(IF(MMULT((Grades!E2:E293="D")*(Grades!G2:G293>0),1)>0,Grades!G2:G293))


Note: The "#N/A"'s in the reference range has to be error trapped using "IFerror,,0" before applying non-array solution.

Thanks