View Single Post
 
Old 01-01-2017, 02:21 PM
adisco adisco is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Jan 2017
Posts: 2
adisco is on a distinguished road
Default 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
Reply With Quote