Weird formula issue

[ghard1]

I have a worksheet with a formula which I have used successfuly in the past. However, when I add more data to my data file, the formula ceases to work. The formula I am using is:

=MAX(IF(Data!$H$2:$H$2000="male",Data!$AX$2:$AX$2000))

Where Column H contains either "Male" or "female" and is formatted as "general" and Column AX is a time and formatted as "custom: [mm]:ss

The formula will work if I sort the data by the H column with "male" below females, but not vice versa.

By using the formula:
=average(data!ax2:ax2000)

I do get an answer.

Any ideas why this formula won't work?

Thanks in advance for all your help!

 

[Scott Huish]

What does "won't work" mean exactly? Do you get an error? Do you get an answer different from what you expected? If so, what answer did you expect, what answer did you get?

 

[barry houdini]

Have you confirmed the formula with CTRL+SHIFT+ENTER so that curly braces appear around it in the formula bar?

 

[ghard1]

I get 00:00 and I should get 185:00

which is what I get as long as I sort the data by column H and decending

 

[ghard1]

I did not enter it as an array. Must I? I forgot to mention that the formula is in a merged cell. When I try to enter it in an array excel says I cannot enter an array in a merged cell.

Also, why would the formula work correctly when my data worksheet contained 60 entries (I now have 1300)?

Thanks

 

[ghard1]

I just noticed that I had previously entered the formula as an array when it worked. I also figured out how to fix it. If I unmerge the cells, enter the formula as an array and then re-merge the cells it works! Thanks for the heads up Barry!