I have most of the new functions working but can't seem to get the FILTER function to show numbers in a range. Say numbers above 2 and below 10. It shouldn't be as hard as I'm making it.
FILTER(A,A>2,A<10)
Filters out the numbers below 2 but not the numbers over 10
All help will be greatly appreciated.
Frank
Mac mini (M2 Pro, 2023)
You can get away with using multiplication for AND, e.g.,
=FILTER(A,(A≥2)*(A≤10))
But using + for OR as you might in Excel, while it gives the expected results, throws a blue triangle warning 'The formula uses a Boolean in place of a Number'.
The most reliable and readable (though slightly more verbose) approach is to use AND or OR explicitly within a MAP, e.g.,
=FILTER(A,MAP(A,LAMBDA(val,AND(val≥2,val≤10))))
OR works similarly, e.g.,
=FILTER(A,MAP(A,LAMBDA(val,OR(val<2,val>10))))
This approach makes the logic explicit. It also makes it easy to add more conditions as needed.
SG
You can get away with using multiplication for AND, e.g.,
=FILTER(A,(A≥2)*(A≤10))
But using + for OR as you might in Excel, while it gives the expected results, throws a blue triangle warning 'The formula uses a Boolean in place of a Number'.
The most reliable and readable (though slightly more verbose) approach is to use AND or OR explicitly within a MAP, e.g.,
=FILTER(A,MAP(A,LAMBDA(val,AND(val≥2,val≤10))))
OR works similarly, e.g.,
=FILTER(A,MAP(A,LAMBDA(val,OR(val<2,val>10))))
This approach makes the logic explicit. It also makes it easy to add more conditions as needed.
SG
Hey Frank, you can try using this formula: FILTER(A, (A > 2) * (A < 10)). The asterisk acts as an 'AND' condition, filtering out numbers that are both greater than 2 and less than 10. Hope this helps!
Hi Frank,
Perhaps use the Filter feature instead of the FILTER function.
Formula in C2 is IF(AND(B2≥2, B2≤10),"Show","")
Organise (on the right) > Filter
Result:
Hide Column C
Tested in Numbers 14.4 under macOS Sequoia 15.4.1
Regards,
Ian.
Thank you. All of those work. I've never used the LAMBDA Function as I can't really get the use of it in my head. I've watched a lot of videos using it in Excel and Numbers but still don't understand it.
I wish there was a channel or two that did for Numbers what the myriad of channels do for Excel. I thunk that would make it a lot easier for more people to use it.
Frank
franks4business wrote:
I've never used the LAMBDA Function as I can't really get the use of it in my head.
LAMBDA is new to many of us here. Don't let the Greek name fool you into thinking LAMBDA is more complicated than it is. It's just a container for an inline custom function. Once it's set up and its parameters defined then you can feed it different values.
In my example above MAP feeds it the values in column A one by one, applying the same logic each time.
SG
This function is new to me but this seems to work
FILTER(A,(A≥2)×(A≤10)=0)
Thanks. That sorts out the numbers between 2 and 10 leaving the rest of them to spill. I hadn't thought of using that but I can see where it;d be very handy for some sheets.
Thanks, Frank
Oops. I saw "above and below" and got into my head "below 2 and above 10" instead of "between 2 and 10".
Filter showing numbers above a number and below a number
