Error: AGGREGATE_doesn't ignore hidden rows for particualr array range

[adisco]

Dear all

Please find bellow one issue I am encountering with AGGREGATE function, I don't succeed to make formula ignoring hidden rows

Imput data:
column B (B3:B9) --> text strings in the next format : 24 ± 6 (18...30) μm

Desired Output Data:


- search in cells B3:B9, between the "..." and close parenthesis ")" [ meaning ...30) ] and return max value
- don't take into account hidden rows/filtered cells

My solution:
- isolate text between "..." and close parenthesis ")" using MID & FINF functions
MID(B3:B9;FIND("...";B3:B9)+3;FIND(")";B3:B9)-FIND("...";B3:B9)-3)
result ==> array {"30";"26";"30";"70";"60";"100";#N/A}
- transform text to number (multiplying by *1)
- apply AGGREGATE(14;7;RANGE;1) - ignore error and hidden cells

The encountered problem:
I don't succeed to make formula ignoring hidden rows (I always get the same result 100)
Does anybody have an explanation for this issue ?

Thanks a lot for any support.

Attached the excel file

without filter : max value = 100 for 100 expected

FORMULA=AGGREGATE(14;7;(MID(B3:B9;FIND("...";B3:B9)+3;FIN D(")";B3:B9)-FIND("...";B3:B9)-3)*1);1)=AGGREGATE(14;7;C3:C9;1)RETURNED MAX VALUE100100RANGE OF VALUES24 ± 6 (18...30) μm3022 ± 4 (18...26) μm2625 ± 5 (20...30) μm3047,5 ± 22,5 (25...70) μm7042,5 ± 17,5 (25...60) μm6075 ± 25 (50...100) μm100#N/A#N/A

with filter : max value = 100 for 70 expected

FORMULA=AGGREGATE(14;7;(MID(B3:B9;FIND("...";B3:B9)+3;FIN D(")";B3:B9)-FIND("...";B3:B9)-3)*1);1)=AGGREGATE(14;7;C3:C9;1)RETURNED MAX VALUE10070RANGE OF VALUES47,5 ± 22,5 (25...70) μm70

[adisco]

I got the solution finally:

Pragmatic solution:
Use a helper column to extrat the number then use SUBTOTAL(); So say your helper column was column C you would use =SUBTOTAL(104,C3:C500) – by Scott Craner

why aggregate function doesn't ignore hidden cells:
Neither the aggregate or the subtotal works with the string parse, that needs to be done prior to the formula. – by Scott Craner