is there a MaxIF function ?

Posted by Moses on October 02, 2001 10:49 PM

I have 2500 rows of data with the queue name in one column and the queue length in another column. This is the way that the data is returned from a query, and can't be changed. For simplicity's sake, I'll call the column's A and B...

A1= Accounts
A2= Accounts_Password
A3= Accounts_Retention
A4= Accounts
A5= Accounts_Password

and so on till row 2564

In Row B I have the queue wait time. eg:

B1= 1851
B2= 102
B3= 5814
B4= 1905
B5= 189

I need to calculate the maximum queue wait for each queue. I have tried using the dmax() function, however this is giving false results due to the similarity in queue names. It is returning data from one of the "Accounts_Password" rows when I am looking for the max of "Accounts".

Put simply, I need either the equivalent of a maxif function, or a way to get the dmax function to only return an exact match. Please help, I'm pulling out lots of hair here ;)

Posted by Aladin Akyurek on October 03, 2001 12:24 AM

A fast check on DMAX using your sample data produced indeed a wrong result.

The following array formula will do the job:

=MAX(IF(A5:A9=A2,B5:B9))

where A5:A9 houses queue names, B5:B9 wait numbers, and A2 the criterion (in these "Accounts").

Note. In order to array-enter a formula, you need to hit CONTROL+SHIFT+ENTER at the same time, not just ENTER.

Aladin

=========