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
=========
