# MEDIAN column of AVERAGEIFS and #DIV/0!

Copper Contributor

# MEDIAN column of AVERAGEIFS and #DIV/0!

I'm a little confused on this.

I have an array of data that was generated by conditional "AVERAGEIFS" with locations by row and differing conditions by column. The data is pulled from a different page.

=AVERAGEIFS(Data!\$AL:\$AL,Data!\$A:\$A,"FG-*",Data!\$F:\$F,"CRITERIA1",Data!AL:AL,"<100",Data!AL:AL,">0",Data!\$G:\$G,"CRITERIA2")

This part works great

I'm trying to get basic statistics on each column.

Not every location has the conditions so I have #DIV/0! errors mixed through the data.

I am trying to get the median of the column. (and am hoping STDEV functions the same way)

Despite what MS says... =MEDIAN(C4:C88)  just gives me a #DIV/0! error.

I've tried multiple IF functions but it seems that having the criteria data in the same column as the range for MEDIA is causing issues.

Any ideas?

Thanks

4 Replies

# Re: MEDIAN column of AVERAGEIFS and #DIV/0!

You may try

``=AGGREGATE(17,6,C4:C88,2)``

# Re: MEDIAN column of AVERAGEIFS and #DIV/0!

Thanks,  @SergeiBaklan

Aggregate worked

=AGGREGATE(12,4,D:D)

# Re: MEDIAN column of AVERAGEIFS and #DIV/0!

Okay. Second parameters 4 ignores nothing, if to ignore errors it shall be 6.

# Re: MEDIAN column of AVERAGEIFS and #DIV/0!

That was for a clean data set,
I used 3 (=AGGREGATE(12,3,E\$2:E\$87)) for a calculated data set
Thanks Again