SUBTOTAL on filtered data issue

[Johnsie007]

Hi,

I have a Subtotal array formula that is applied to a range of filtered data.

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

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

Essentially, 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.95 and -0.97. The value in cell AH8 is -0.95.

The formula is returning a value of 0.

It should return the highest possible value that is below -0.95. i.e. -0.96

Is there something obvious that I am missing?

Any help would be greatly appreciated.

Thanks.

 

[nemmi69]

is this any good?

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

 

[Johnsie007]

Hi Nemmi69,

No...It returns 0.97

 

[nemmi69]

can multiply by -1 to convert back to negative

 

[Johnsie007]

The value it needs to return is -0.96. i.e. The highest possible value below -0.95.

+-0.97 is the lowest possible value.

 

[nemmi69]

So ignore my formula you need it to look for MIN

 

[Johnsie007]

Can you please provide an example...when I replace MAX with MIN it still returns 0.97

 

[nemmi69]

Without seeing the code some of this is guess work but try -

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

 

[Johnsie007]

This still returns the minimum value i.e. -0.97....I need to find the MAX value in the range that is less than the value in AH8. (-0.95).

It should return -0.96.

 

[Johnsie007]

Can anyone please help...