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.

 

[Johnsie007]

Analyser - Copy (6).xlsb

Shared with Dropbox

www.dropbox.com

 

[Johnsie007]

Kevin,

I really appreciate you taking the time to look at this.

I have set the auto-filters in the saved spreadsheet.

The value I am hoping to return in cell AH9 is -0.0644881951297712.

 

[kevin9999]

Firstly, thanks for providing your actual file - it makes life so much simpler.
Secondly, I'm struggling to get my head around precisely what you're hoping to achieve. In post #1 you said you were trying to

find is the maximum value in the filtered range AH15:AH21739 that is less than the value in cell AH8.

In the supplied file, I put your value -0.06448820 in cell AH8, and my formula from post #11 in cell AH9. This is the result I get:

Analyser - Copy (6).xlsb
	AG	AH
8	Outliers	-0.0644882
9	Value	-0.064578518
Analyser 644

Cell Formulas
Range	Formula
AH9	AH9	=MAX(IF(AH15:AH7366<AH8,AH15:AH7366,""))

Which to me seems to be the correct result, because to return -0.0644881951297712 instead (per your post #22) would not be less than the value in cell AH8 - in fact I think it's the same value, just to more decimal places. The actual value that is the maximum value - that is less than the value in cell AH8 would be:

-0.064578518​

Unless I'm completely misunderstanding your intentions, for which I apologise.