# Ignoring null values when finding the maximum from an array

#### andydarlo

##### New Member
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.

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### jbeaucaire

##### Well-known Member
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)), "")

Last edited:

#### andydarlo

##### New Member
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...

Code:
``````=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

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

Replies
1
Views
126
Replies
0
Views
138
Replies
2
Views
341
Replies
8
Views
332
Replies
1
Views
264

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,512
Messages
5,832,178
Members
430,114
Latest member
kefier

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back