I’m still having way too much fun on Pastebin.com. Below is a formula I started while working out, but ended up getting a little more fancy after I tried to clean it up to run quicker. It calculates the next highest prime number after a given number (example:
=NEXTPRIMENUMBER(100) = 101)
There are some regular Excel Array formulas that can be used to test true or false on integers being prime, but they are limited to testing values matching row counts, so no higher than 1.05 million (boring!). I did run into a weird VBA bug where I discovered the vba Function MOD ALWAYS converts its values to LONG parameters. I discussed this a little on Stack Overflow as this became an issue when trying to do bigger numbers.
Anyway, check this out if you’re bored or if you’re dying to know what the next highest prime number is after 10 trillion (it’s 10,000,000,000,037). It does this calculation in under 4 seconds on my laptop. Initially it was taking about 5 seconds to do a billion, but I cleaned up some redundancies, and it’s pretty good now. If anyone can enhance (using EXCLE vba), I’d be curious to see.