Searching for MAX less than X via SUBTOTAL on filtered Data problem. Please Help!

[AmatuerNoob]

Hi,

I have a MAX(SUBTOTAL array formula that is applied to a range of filtered data.

The purpose is to find the maximum value of a filtered range which is less than X.

It works when the value of X is positive, however, it doesn't seem to work when the value of X is negative.

{=MAX(SUBTOTAL(104,OFFSET(AH15,ROW(AH15:AH21739)-MIN(ROW(AH15:AH21739)),0,1,1))*(AH15:AH21739<=AH8))}

What I am trying to find is the maximum value in the filtered range AH15:AH21739 that is less than the value in cell AH8.

The values in the range are between 0.003187 and -0.411742. The value in cell AH8 is -0.0326537

The formula is returning a value of 0. As shown in cell AH9. (Screenshot attached)

It should return the highest possible value that is below -0.0326537. i.e. -0.06

Is there something obvious that I am missing?

Any help would be greatly appreciated.

Thanks.

Analyser Snapshot.PNG

[XLent]

The formula is returning a value of 0. As shown in cell AH9. (Screenshot attached)

It should return the highest possible value that is below -0.0326537. i.e. -0.06

Is there something obvious that I am missing?

any value which is either hidden and/or > criteria will, within the array, return 0 as either/both sides of the multiplication are * 0, which will exceed the legitimate -ve results.

Assuming, per profile, XL2010 you could try something along lines of:

Please Login or Register  to view this content.