maxif, minif functions

**firefly2k8** · 10-21-2010, 10:04 AM

Hi, I am trying to get these functions working but having problems.

From a different thread we have:

If you have the unique items listed somewhere on the same sheet or in another sheet, then you can use Min(if()), Max(if()) and Mode(If()) array formulas...

e.g.

if you database is in Sheet1, A1:B100 where A1:A100 lists the items, B1:B100 lists the prices then:

=MIN(IF($A$1:$A$100=X1,$B$1:$B$100)) will give the miimum from column B of the item in column A that matches the entry in X1

after entering the formula, confirm it with CTRL+SHIFT+ENTER not just ENTER and copy down.

replace Min for Max and Mode to get those relevant results.

but I have a list of indentifiers is column A and a unique identifier in X1 and a numerical data item in column B. This returns a NUM!! error. I am using CTRL+SHIFT+ENTER.

Thanks

**DonkeyOte** · 10-21-2010, 10:22 AM

In your version of the above formula are you using entire column references by any chance ? (ie $A:$A as opposed to $A$1:$A$100)

If you are and you're using a version pre XL2007 you will receive the error you describe.
Arrays are inefficient - keep ranges as lean as possible at all times.

**firefly2k8** · 10-21-2010, 11:32 AM

You are the smartest donkey I know.

I was using the entire column, thanks

**DonkeyOte** · 10-21-2010, 01:29 PM

Sound like you need to get out there and meet more donkeys

Glad you have resolved

With Arrays if the precedent ranges change regularly and are potentially large then it's often wise to use Dynamic Named Ranges (created with INDEX rather than OFFSET if possible).
Performance wise this would be better than building in lots of "spare capacity" via enlarged hard wired ranges from the get-go.