I'm sure this should be easy, but....

I have a datadump from a survey where all answers are numerical, but n/a answers are known as a mix of #NULL and 0.

With this data I am trying to work out the maximum, average and minimum numbers for each quesiton excluding the nils and #NULL's.

The NULL's don't seem to create any issues (in that the formulas seem to ignore them), and maximum is easy as the nils have no affect. Likewise AVERAGEIF allows the nils to be excluded on the average, but I need something equivalent to MINIF to set a "<>0" term, and yet this formula doesn't seem to exist....

Unfortunately I need to keep the nils for some other analysis (otherwise you could just do a find and replace with ""), so can anyone think of a way around this?

Thank you in advance!

Simon

If your range is, say, A1:A10 then:

=MIN(IF(IFERROR(A1:A10,0)>0,A1:A10))

Has to be entered using Ctrl-Shift-Enter, not just Enter and you have to be on Excel 2007 or later.

Thanks Andrew - works a treat.

I remember you talking about the black magic of array formulas a while back - looks like I need to get my head around them!

There is a non-array way of doing it:

=SMALL(A1:A10,COUNTIF(A1:A10,0)+1)

But it throws an error if any of the cells contain an error or if none of the cells are >0.

Hip hip... Array?

Bliney, AndrewR is quiet for months and then an excel question appears and he is onto it like a butcher's dog.

If we mention Porsche 911 will Brewster also appear?

I've been checking in here every once in a while, but there don't seem to have been many fights debates for me to take part in.

Anyway, I'm down with the kids now - so if anybody wants to know what I'm up to I'd recommend checking out my brilliant and witty Twitter feed - @excelpope