Ignoring null values when finding the maximum from an array

andydarlo

New Member
Joined
Aug 10, 2010
Messages
2
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.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
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
Joined
Aug 10, 2010
Messages
2
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
Joined
May 8, 2002
Messages
6,012
There are a variety of syntaxes usable in SUMPRODUCT, each with varying results. If yours is now working, then good work. Onward...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,600
Messages
5,597,114
Members
414,125
Latest member
iQQ

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?

Disable AdBlock

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
Top