Sorting and Filtering

[nikolaki]

Hi all:

Here is my formula:

Thanks in advance for any help!

=IF(J11=">=",
SORT(FILTER(A:F,(F:F>=I2)*(ABS(E:E)>I7)),6,1,FALSE),
SORT(FILTER(A:F,(F:F<=I3)*(ABS(E:E)>I7)),6,-1,FALSE))

I want to filter by values in F and Absolute values in E, and then and then sort results by Column 6, by ascending order if J11 =">=", else descending order .

I am getting a #VALUE! error. Uggh!

 

[Alex Blakenburg]

The most likely cause of your #VALUE error is your ABS function in turn caused by having a text value or empty string "" in column E.
See if this helps.

=IF(J11=">=",
SORT(FILTER(A:F,(F:F>=I2)*(IFERROR(ABS(E:E),0)>I7)),6,1,FALSE),
SORT(FILTER(A:F,(F:F<=I3)*(IFERROR(ABS(E:E),0)>I7)),6,-1,FALSE))

Or using LET

=LET(absV,IFERROR(ABS(E:E),0),
IF(J11=">=",
SORT(FILTER(A:F,(F:F>=I2)*(absV>I7)),6,1,FALSE),
SORT(FILTER(A:F,(F:F<=13)*(absV>I7)),6,-1,FALSE)))

 

[nikolaki]

The most likely cause of your #VALUE error is your ABS function in turn caused by having a text value or empty string "" in column E.
See if this helps.

=IF(J11=">=",
SORT(FILTER(A:F,(F:F>=I2)*(IFERROR(ABS(E:E),0)>I7)),6,1,FALSE),
SORT(FILTER(A:F,(F:F<=I3)*(IFERROR(ABS(E:E),0)>I7)),6,-1,FALSE))

Or using LET

=LET(absV,IFERROR(ABS(E:E),0),
IF(J11=">=",
SORT(FILTER(A:F,(F:F>=I2)*(absV>I7)),6,1,FALSE),
SORT(FILTER(A:F,(F:F<=13)*(absV>I7)),6,-1,FALSE)))

Thanks a ton! You rule!

 

[Alex Blakenburg]

Thanks for the feedback. Glad I could help.