Ignoring null values when finding the maximum from an array

[andydarlo]

Hi there,

Apologies if the title does not reflect what I am actually wanting to do, but the terminology is a little new to me.

I have just taught myself how to use arrays from this fantastic forum, and am finding them to be very useful.

I am a school teacher and have a sheet of data which has tests containing different, but often repeated, learning tasks in the rows and pupil names in the columns. On a second tab, I have a summary list of the learning tasks and I am using the following function:

{=MAX(IF('Scores by Criteria'!$B$2:$B$10=$B13,'Scores by Criteria'!AF$2:AF$10))}

to find the maximum score a pupil has gained for a given learning task across several tests. It is working fine, except it is outputting a zero value where the data is null. I require a zero value where the data is a numerical zero, but I require the cell to remain blank where the pupil has no value at all.

I'm afraid that I cannot seem to figure out a way forward on this, perhaps as this is all so new to me, and wonder if anyone can help? I have a feeling the ISBLANK function may come into it somewhere, but am at a bit of a loss from there on in.

All help gratefully appreciated....

Andrew.

 

[jbeaucaire]

One way, also an array formula:

=IF(SUMPRODUCT(('Scores by Criteria'!$B$2:$B$10=$B13)*('Scores by Criteria'!$AF$2:$AF$10<>""))>0,
MAX((('Scores by Criteria'!$B$2:$B$10=$B13) * 'Scores by Criteria'!$AF$2:$AF$10)), "")

 

[andydarlo]

Thanks for the reply.

I tried the formula you gave, but it threw up an error when the data range contained some null and some zero values.

I had a look at the formula and wondered if the '*' in the TRUE part of the IF statement was correct as I wasnt convinced there should be a multiplication there.

I tweaked the formula a little, and the following seems to be returning the values I am requiring...

=IF(SUMPRODUCT(('Scores by Criteria'!$B$2:$B$1000=$B13)*('Scores by Criteria'!AE$2:AE$1000<>""))>0,
MAX(IF('Scores by Criteria'!$B$2:$B$1000=$B13,'Scores by Criteria'!AE$2:AE$1000)),"")

Thanks again for your contribution, I could not have got a working formula without it. Does my formula look like it should return values correctly?

Andrew

 

[jbeaucaire]

There are a variety of syntaxes usable in SUMPRODUCT, each with varying results. If yours is now working, then good work. Onward...