Thread: Mode Array dual criteria date range

I've been scouring the forums for help with this and all the similar solutions to my problem aren't working. I've tried,

{=MODE.MULT(IF($A$7:$A$1047=$A7,IF($B$7:$B$1047>=$A$3,IF($B$7:$B$1047<=$A$4,C7:C1047))))}
Specific to my example sheet attached.

http://www.pcreview.co.uk/forums/mod...-t3433187.html

and

http://www.teachexcel.com/excel-help...to.php?i=32210

and many variations of these examples and can't get it to work. I've only been able to either get .13 or .11 as an answer when I get one but that is for the entire column. I've also tried switching the Mode to Mode.Mult since I'm using 2010 but doesn't seem to help.

My end goal is to get the Mode of the Rate for each Tag number for each time period. Please help if you can this formula is slowly taking over my mind. I've attached what I'm working with trying to come up with the answer.

Thank you ahead of time.

Mode.Array.xls

Hi,
See attached a solution with the function Mode to get the most common of the Rate for each Tag number for each time period.
The drop down list can be change to the complete list without any problem
Hope this will helps
Best regards

You have made my day with this. Thank you so much for the help.

It works 90% now. The formula for some reason isn't capturing some of the rates though. For example Tag no. 2587 and the 2nd Period for Tag no. 5684. Do you have any ideas with these?

Mode_Array_jpr73(1).xls

I found the problem. It is a rounding error. The mode won't work with too many decimals deep. I added a round to 3 digits into the formula and now it works 100%. Thank you again so much for your help. Couldn't have done it without you jpr73. Your the best.