FILTER formula to display nothing if value is between 2 values Google sheets

user_15874516 0
Viewed 53

I have a FILTER formula to display data based on several conditions:

=FILTER('Raw Batter'!I2:K, 'Raw Batter'!B2:B>C4, 'Raw Batter'!B2:B < C5, 'Raw Batter'!D2:D <> "", 'Raw Batter'!A2:A>=C6,'Raw Batter'!A2:A<=C7 )

​​ which works fine, what I am struggling to do is include an IF AND formula within it to display "" if values are between a range of percentages, like this:

('Raw Batter'!I2:I> -10%,'Raw Batter'!I2:I< 10%)

I have tried all sorts of combinations and cannot get anything to work... Any advice would be awesome!

Example sheet here:

https://docs.google.com/spreadsheets/d/1j_xGf_vO5sEFakJPfuz2hG-xsgRy8AS2L3KamLsLEQY/edit?usp=sharing

The formula is in I18 of 'Batter" tab

google-sheets
1 Answers

try:

=FILTER('Raw Batter'!A2:K,
 C4 < 'Raw Batter'!B2:B, 'Raw Batter'!B2:B < C5,
 'Raw Batter'!D2:D <> "",
 'Raw Batter'!A2:A>=C6,'Raw Batter'!A2:A<=C7,
 'Raw Batter'!I2:I*1> -10%, 'Raw Batter'!I2:I*1< 10%)

enter image description here

UPDATE 1:

=FILTER('Raw Batter'!A2:K,
 C4 < 'Raw Batter'!B2:B, 'Raw Batter'!B2:B < C5,
 'Raw Batter'!D2:D <> "",
 'Raw Batter'!A2:A>=C6,'Raw Batter'!A2:A<=C7,
 ('Raw Batter'!I2:I*1< -10%)+('Raw Batter'!I2:I*1> 10%) )

enter image description here

UPDATE 2:

=ArrayFormula({IF((FILTER('Raw Batter'!I2:I*1,
 'Raw Batter'!B2:B>C4, 'Raw Batter'!B2:B < C5,
 'Raw Batter'!D2:D <> "",
 'Raw Batter'!A2:A>=C6,'Raw Batter'!A2:A<=C7)>10%)+(FILTER('Raw Batter'!I2:I*1,
 'Raw Batter'!B2:B>C4, 'Raw Batter'!B2:B < C5,
 'Raw Batter'!D2:D <> "",
 'Raw Batter'!A2:A>=C6,'Raw Batter'!A2:A<=C7)<-10%),FILTER('Raw Batter'!I2:I,
 'Raw Batter'!B2:B>C4, 'Raw Batter'!B2:B < C5,
 'Raw Batter'!D2:D <> "",
 'Raw Batter'!A2:A>=C6,'Raw Batter'!A2:A<=C7), ), FILTER('Raw Batter'!J2:K,
 'Raw Batter'!B2:B>C4, 'Raw Batter'!B2:B < C5,
 'Raw Batter'!D2:D <> "",
 'Raw Batter'!A2:A>=C6,'Raw Batter'!A2:A<=C7)})

enter image description here

UPDATE 3:

=ArrayFormula(IF((FILTER('Raw Batter'!I2:I*1,
 'Raw Batter'!B2:B>C4, 'Raw Batter'!B2:B < C5,
 'Raw Batter'!D2:D <> "",
 'Raw Batter'!A2:A>=C6,'Raw Batter'!A2:A<=C7)>10%)+(FILTER('Raw Batter'!I2:I*1,
 'Raw Batter'!B2:B>C4, 'Raw Batter'!B2:B < C5,
 'Raw Batter'!D2:D <> "",
 'Raw Batter'!A2:A>=C6,'Raw Batter'!A2:A<=C7)<-10%),FILTER('Raw Batter'!I2:K,
 'Raw Batter'!B2:B>C4, 'Raw Batter'!B2:B < C5,
 'Raw Batter'!D2:D <> "",
 'Raw Batter'!A2:A>=C6,'Raw Batter'!A2:A<=C7), ))

enter image description here

user_56326290
Related