DMAX with multiple criteria

[Dandas]

At the contact centre I work in we measure the longest delay a call waited each day, and then at the end of the week the longest delay of the week is measured.

What I need to know is can the maximum delay be found by week over several weeks. eg.

Delay by Day Week Result I Want
1256 1 1256
123 1 1256
325 1 1256
600 1 1256
456 2 958
958 2 958
854 2 958
787 2 958
789 3 987
456 3 987
987 3 987
456 3 987
321 4 321

Thanks in advance.

 

[DaleWatson123321]

Dandas,

Having had significant experience with MULTIPLE criteria for database formulas, I know I can provide the help you need.

However, I appreciate that you can't possibly define ALL of your needs here at Tek-Tips. Therefore, if you'd like to email me your file, I'll set up the formulas and criteria and send the file back via return email.

If you happen to have sensitive data, replace it with fictitious data that still reflects the type of data you're working with.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[GlennUK]

Also a simple array formula will do what you want. Assuming your delays are in column A, and the week number is in column B, your example above could be done by entering this formula in column c ( enter in C2, and copy down ), entered using CTRL-SHIFT-ENTER instead of ENTER ...

=MAX(IF($B$2:$B$14=B2,$A$2:$A$14,0))

When this has been entered as an array formula, Excel will put {} around the formula to signify that it is an array formula.

Glenn.