SUBTOTAL on filtered data issue

Johnsie007 · Apr 13, 2023

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.

kevin9999 · Apr 14, 2023

Johnsie007 said:
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.

Book1

AH

AI

8

-0.95

9

10

-0.96

Is this it?

11

12

13

14

Header

15

-0.97

16

-0.96

17

-0.95

21

-0.97

22

-0.96

23

-0.95

27

-0.97

28

-0.96

29

-0.95

33

-0.97

34

-0.96

35

-0.95

39

-0.97

40

-0.96

Sheet1

Cell Formulas
Range	Formula
AH10	AH10	=MAX(IF(AH15:AH21740<AH8,AH15:AH21740,""))

Johnsie007 · Apr 14, 2023

Thanks Kevin, this is getting closer....

When I use this formula on my data it partially works..... it returns a negative value less than the value in cell AH8, however, it doesn't match any of the actual values in my dataset.

I need it match an exact value as I use this as a reference point.

Why would it be returning a value that isn't actually in the filtered dataset?

kevin9999 · Apr 14, 2023

Johnsie007 said:
I need it match an exact value as I use this as a reference point.

The "exact" value should be (according to your previous advice) the maximum value in the range that is less than the value of AH8. Is that not what it's returning?

Could you provide your actual dataset via the XL2BB add in, or possibly share via Dropbox, Google Drive or something similar?

Johnsie007 · Apr 14, 2023

			Max	0.00318671
			Median	-0.17670380
			Min	-0.41174167
			Mean	-0.20718480
			St. Dev	0.10545696
			IQR	0.08726553
			Outliers	-0.03265375
00/01/1900			Value	-0.03272303
			%	-10.27	Annualised
			Total Agg.	2561.95	0
			Act. Agg.	2452.95	0
			%	0.96
Stock	Value	1 Yr	Return Diff	Value Diff	Final Diff
OVT.AX^H22	101847	16.51	70.56	-0.35047891	-201
OVT.AX^H22	101231	6.26	62.27	-0.37527032	-166
OVT.AX^H22	101901	5.54	59.97	-0.34924514	-172
OVT.AX^H22	101901	5.54	59.97	-0.34924514	-172
OVT.AX^H22	101901	1.43	56.72	-0.34924514	-162
OVT.AX^H22	103250	1.43	57.62	-0.33705198	-171
OVT.AX^H22	104605	-7.91	48.11	-0.34249203	-140
OVT.AX^H22	96525	-2.19	55.19	-0.39678579	-139
OVT.AX^H22	96517	-5.09	54.19	-0.40257284	-135
OVT.AX^H22	93794	-2.30	56.43	-0.41174167	-137
OVT.AX^H22	93794	-2.30	56.43	-0.41174167	-137
OVT.AX^H22	93794	2.17	60.34	-0.41174167	-147
OVT.AX^H22	80099	12.80	55.90	-0.16990719	-329
OVT.AX^H22	74621	21.15	65.56	-0.24335831	-269
OVT.AX^H22	74621	21.15	65.56	-0.24335831	-269
OVT.AX^H22	74621	22.09	65.55	-0.24335831	-269
OVT.AX^H22	74623	19.27	64.78	-0.28091473	-231
OVT.AX^H22	70506	25.29	70.20	-0.29110714	-241
OVT.AX^H22	71876	25.81	70.78	-0.24133367	-293
OVT.AX^H22	82804	4.81	59.26	-0.17125432	-346
OVT.AX^H22	80067	8.42	63.28	-0.20537674	-308
OVT.AX^H22	80067	8.42	63.28	-0.20537674	-308
OVT.AX^H22	80067	8.42	63.28	-0.20537674	-308
OVT.AX^H22	81425	6.59	46.36	-0.13667089	-339
OVT.AX^H22	79379	11.13	40.80	-0.12648257	-323
OVT.AX^H22	82082	5.69	36.90	0.00318671	11580
OVT.AX^H22	82082	5.69	36.90	0.00318671	11580
OVT.AX^H22	82082	3.99	35.20	0.00318671	11045
OVT.AX^H22	79972	4.93	41.10	-0.08915189	-461
OVT.AX^H22	81339	3.15	38.66	-0.06448820	-599
OVT.AX^H22	81339	3.15	38.66	-0.06448820	-599
OVT.AX^H22	81339	3.15	34.36	-0.06448820	-533
OVT.AX^H22	82017	1.43	36.16	-0.15289954	-236
OVT.AX^H22	82018	-4.54	32.91	-0.19223871	-171
OVT.AX^H22	82018	-4.54	32.91	-0.19223871	-171
OVT.AX^H22	82018	-0.28	37.18	-0.19223871	-193
OVT.AX^H22	80371	-1.17	36.00	-0.20299782	-177
OVT.AX^H22	81043	-2.87	33.67	-0.19969707	-169
OVT.AX^H22	81044	-8.89	26.28	-0.19969542	-132
OVT.AX^H22	82409	-11.25	23.09	-0.16527119	-140
OVT.AX^H22	82409	-11.25	23.09	-0.16527119	-140
OVT.AX^H22	82409	-10.40	24.40	-0.16527119	-148
OVT.AX^H22	83765	-14.37	19.97	-0.15159527	-132
OVT.AX^H22	83767	-15.20	22.06	-0.15885560	-139
OVT.AX^H22	83752	-15.20	23.81	-0.16956813	-140
OVT.AX^H22	84462	-18.36	25.77	-0.17670380	-146
OVT.AX^H22	83785	-17.69	25.44	-0.16947184	-150
OVT.AX^H22	83785	-17.69	25.44	-0.16947184	-150
OVT.AX^H22	83785	-17.69	28.19	-0.16947184	-166
OVT.AX^H22	84488	-15.06	37.00	-0.16623118	-223
OVT.AX^H22	83811	-12.70	37.79	-0.16231240	-233
OVT.AX^H22	79727	-9.06	42.16	-0.19255270	-219
OVT.AX^H22	81065	-10.61	42.45	-0.14567420	-291
OVT.AX^H22	79717	-9.06	43.77	-0.15609278	-280
OVT.AX^H22	79717	-9.06	43.77	-0.15609278	-280
OVT.AX^H22	79717	-9.06	41.80	-0.15609278	-268
OVT.AX^H22	81088	-7.17	42.69	-0.15698285	-272

kevin9999 · Apr 14, 2023

Which is column H, and which cell is AH8?

Johnsie007 · Apr 14, 2023

Kevin,

When I copy my data across to a new sheet, with no filtered data and just the values, it works and returns an exact match.

Something is going wrong when it is applied to the filtered dataset.

Johnsie007 · Apr 14, 2023

			Max	0.00318671
			Median	-0.17670380
			Min	-0.41174167
			Mean	-0.20718480
			St. Dev	0.10545696
			IQR	0.08726553
			Outliers	-0.03265375
00/01/1900			Value	-0.06448820
			%	-10.27	Annualised
			Total Agg.	2561.95	0
			Act. Agg.	2452.95	0
			%	0.96
Stock	Value	1 Yr	Return Diff	Value Diff	Final Diff
OVT.AX^H22	101847	16.51	70.56	-0.35047891	-201
OVT.AX^H22	101231	6.26	62.27	-0.37527032	-166
OVT.AX^H22	101901	5.54	59.97	-0.34924514	-172
OVT.AX^H22	101901	5.54	59.97	-0.34924514	-172
OVT.AX^H22	101901	1.43	56.72	-0.34924514	-162
OVT.AX^H22	103250	1.43	57.62	-0.33705198	-171
OVT.AX^H22	104605	-7.91	48.11	-0.34249203	-140
OVT.AX^H22	96525	-2.19	55.19	-0.39678579	-139
OVT.AX^H22	96517	-5.09	54.19	-0.40257284	-135
OVT.AX^H22	93794	-2.30	56.43	-0.41174167	-137
OVT.AX^H22	93794	-2.30	56.43	-0.41174167	-137
OVT.AX^H22	93794	2.17	60.34	-0.41174167	-147
OVT.AX^H22	80099	12.80	55.90	-0.16990719	-329
OVT.AX^H22	74621	21.15	65.56	-0.24335831	-269
OVT.AX^H22	74621	21.15	65.56	-0.24335831	-269
OVT.AX^H22	74621	22.09	65.55	-0.24335831	-269
OVT.AX^H22	74623	19.27	64.78	-0.28091473	-231
OVT.AX^H22	70506	25.29	70.20	-0.29110714	-241
OVT.AX^H22	71876	25.81	70.78	-0.24133367	-293
OVT.AX^H22	82804	4.81	59.26	-0.17125432	-346
OVT.AX^H22	80067	8.42	63.28	-0.20537674	-308
OVT.AX^H22	80067	8.42	63.28	-0.20537674	-308
OVT.AX^H22	80067	8.42	63.28	-0.20537674	-308
OVT.AX^H22	81425	6.59	46.36	-0.13667089	-339
OVT.AX^H22	79379	11.13	40.80	-0.12648257	-323
OVT.AX^H22	82082	5.69	36.90	0.00318671	11580
OVT.AX^H22	82082	5.69	36.90	0.00318671	11580
OVT.AX^H22	82082	3.99	35.20	0.00318671	11045
OVT.AX^H22	79972	4.93	41.10	-0.08915189	-461
OVT.AX^H22	81339	3.15	38.66	-0.06448820	-599
OVT.AX^H22	81339	3.15	38.66	-0.06448820	-599
OVT.AX^H22	81339	3.15	34.36	-0.06448820	-533
OVT.AX^H22	82017	1.43	36.16	-0.15289954	-236
OVT.AX^H22	82018	-4.54	32.91	-0.19223871	-171
OVT.AX^H22	82018	-4.54	32.91	-0.19223871	-171
OVT.AX^H22	82018	-0.28	37.18	-0.19223871	-193
OVT.AX^H22	80371	-1.17	36.00	-0.20299782	-177
OVT.AX^H22	81043	-2.87	33.67	-0.19969707	-169
OVT.AX^H22	81044	-8.89	26.28	-0.19969542	-132
OVT.AX^H22	82409	-11.25	23.09	-0.16527119	-140
OVT.AX^H22	82409	-11.25	23.09	-0.16527119	-140
OVT.AX^H22	82409	-10.40	24.40	-0.16527119	-148
OVT.AX^H22	83765	-14.37	19.97	-0.15159527	-132
OVT.AX^H22	83767	-15.20	22.06	-0.15885560	-139
OVT.AX^H22	83752	-15.20	23.81	-0.16956813	-140
OVT.AX^H22	84462	-18.36	25.77	-0.17670380	-146
OVT.AX^H22	83785	-17.69	25.44	-0.16947184	-150
OVT.AX^H22	83785	-17.69	25.44	-0.16947184	-150
OVT.AX^H22	83785	-17.69	28.19	-0.16947184	-166
OVT.AX^H22	84488	-15.06	37.00	-0.16623118	-223
OVT.AX^H22	83811	-12.70	37.79	-0.16231240	-233
OVT.AX^H22	79727	-9.06	42.16	-0.19255270	-219
OVT.AX^H22	81065	-10.61	42.45	-0.14567420	-291
OVT.AX^H22	79717	-9.06	43.77	-0.15609278	-280
OVT.AX^H22	79717	-9.06	43.77	-0.15609278	-280
OVT.AX^H22	79717	-9.06	41.80	-0.15609278	-268
OVT.AX^H22	81088	-7.17	42.69	-0.15698285	-272

kevin9999 · Apr 14, 2023

Johnsie007 said:
Something is going wrong when it is applied to the filtered dataset.

That's strange - in post #11, I actually did use a filtered dataset...

Johnsie007 · Apr 14, 2023

The relevant cell is

Value

-0.06448820

The second sheet shows the result when applied to the simple dataset with no filtering.

kevin9999 · Apr 14, 2023

I can't copy what you've shown in posts 14 & 17 into a sheet (and I don't intend typing the values in) so - could you provide the sheets via the XL2BB add in, or possibly share via Dropbox, Google Drive or any similar file sharing site?

SUBTOTAL on filtered data issue

Johnsie007

New Member

kevin9999

Well-known Member

Excel Facts

Johnsie007

New Member

kevin9999

Well-known Member

Johnsie007

New Member

kevin9999

Well-known Member

Johnsie007

New Member

Johnsie007

New Member

kevin9999

Well-known Member

Johnsie007

New Member

kevin9999

Well-known Member

Similar threads

Forum statistics

Share this page

SUBTOTAL on filtered data issue

New Member

Well-known Member

Excel Facts

New Member

Well-known Member

New Member

Well-known Member

New Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock