I am math-geek-wannabe, if there ever is such a category. During my 3rd year of graduation I went and purchased the volume 2 of Donald Knuth‘s Art of Computer Programming
(assuming the number is in the cell B5) type the below formula and
=IF(MIN(MOD($B$5,ROW(INDIRECT("2:"&INT(SQRT($B$5))))))=0,"not prime","prime")
hit ctrl+shift+enter and bingo, it tells you if the number is prime or not.
how it works?
take a guess…
you are right. It just calculates the minimum of all reminders when the number x is divided by values between 2 and sqrt(x) and sees if it is zero. If so, the number is “not prime”, else, it is a “prime number”.
More random stuff on randomness and numbers
Shuffle a list of values in excel using random numbers
3 Responses to “Filter one table if the value is in another table (Formula Trick)”
What about the opposite? I want a list of products without sales or customers with no orders. So I would exclude the ones that are on the other table.
Good question. You can check for the =0 as countifs result. for example,
=FILTER(orders, COUNTIFS(products, orders[Product])=0)
should work in this case.
PS: I have added this example to the article now.
Hi there!
Could i check if there was a way to return certain fields of the table only?
so based off your example above, i would like to continue to use the 'Products" table as a way to filter out items from my "Orders" table, but only want to show maybe only the "Product" and "Order Value" fields, rather than all 5 fields (sales person, customer, product, date, order value).