Error: AGGREGATE_doesn't ignore hidden rows for particualr array range
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
|