Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How To Check If The Number Is A Prime Number In Excel?
What if you could find out whether a number in your Excel data set is prime or not in less than 5 steps? We will tell you how in this tutorial, but first, let us understand what a prime number is.
A prime number is a number which is divisible only by 1 and itself, and the number 1 is not a prime number. Applying this simple definition, you can use the formula described in the steps to determine if a number is indeed a prime number or not.
Array Formula For Checking Prime Numbers
An array formula simultaneously performs multiple calculations on items in a row or column. Depending on the instructions, the result can be either multiple or single. An example worksheet illustrates how you can use the formula mentioned in the steps to test whether a number is prime.
Step 1 − Select an empty cell next to the one you want to check for a prime number. In this case, we select cell B2.
Step 2 − To check the values for cell A2, enter the following formula in cell B2 and press the Control + Shift + Enter keys to complete the function.
Array formula to check if the number in MS Excel is a prime number =IF(A2=2,"Prime",IF(AND(MOD(A2,ROW(INDIRECT("2:"&ROUNDUP(SQRT(A2),0)))) <>0),"Prime","Not Prime"))
If you want to check the values of other cells in the array, for example, cell C7, you should modify the array formula before pasting −
=IF(C7=2,"Prime",IF(AND(MOD(C7,ROW(INDIRECT("2:"&ROUNDUP(SQRT(C7),0)))) <>0),"Prime","Not Prime"))
Step 3 − In cell B2, "Prime" will appear if the number is prime, and "Not Prime" if not. The array formula will be closed with a set of { } in the formula box, indicating that the function has been successfully performed.
Step 4 − To check for a column of numbers, like those in Column A, simply drag the green box in the corner of active cell B2, where the array function was performed, to the numbers you want. From a list of multiple values, you can identify prime numbers in seconds using this method.
How Does The Formula Work?
As mentioned before, an array formula contains a set of functions to get the correct result. It relies on Excel's MOD function to calculate the remainder on every whole number divisor from 2 to the square root of the test number. If there is always a remainder, then the number is prime.
Following are the instruction set in the formula −
Select the cell number
If the number is 2, it is a prime number; otherwise, go to the next stage.
Determine the number's square root.
Divide the number by all the integers between 2 and the number's square root.
If the remainder in any division is zero, the value is non-prime.
Otherwise, it is the prime number.
Conclusion
Practising Excel daily and learning new tools and tricks improves your skills. There are more such tutorials to help you master the spreadsheet in no time.
4K+ Views
