CrossOver is the for-profit version of WINE (a windows compatibility layer) for Mac and Linux. So you can get the same for free. But paying for CrossOver does have significant advantages: 1) support 2) a much better software package. I should also mention that CodeWeavers is funding the development of WINE, so paying for CO benefits the whole community.
So, what does CO do? Basically, it allows you to run Windows software on your Mac in OSX *without* running a full windows emulator like VirtualBox, Parallels or VMWare. That means that you can run Windows software without needing a copy of Windows. Plus, CO has compatibility modules (they call them 'Bottles') for Windows 98, 2000, XP, Vista, and 7.
An important, though perhaps under documented, point is that you don't really buy CO - you purchase the right to run the currently recent version, plus all updates for the next 12 Months. After that you may continue to use CO, but you can't update, and you are no longer entitled to support unless you purchase a license extension. For a business these terms are quite acceptable. Private users may not require continued support; yet being able to have the most recent version of CO can be worth the admission fee.
Using CrossOver *can* be frustrating, because it assumes you know your way around in the particular brand of Windows your application requires - a presupposition that can prevent you from getting the results you need. If you don't know your way around Windows and the Windows software does not run from the get go (a rarity with old titles), you are pretty much screwed. That's where the service/support subscription comes in, and I've submitted a number of tickets to have them answered within 24 hours. That being said, the customization features of WINE are limited, which is both a boon and a problem if your software requires a particular feature.
How well does it work? Well, if you have legacy software that is standard, straight code and you need to access that software or data, it works really, really well. CodeWeaver's site has a compatibility DB where you can check on-line if an application will run; be advised though that this DB isn't always current, and new versions of CrossOver can break compatibility with older software. If you have legacy software that taxed the system at the time (usually games), used DRM, or non-standard graphics (e.g. GLide), results are mixed.
I was able, for example, to install Homeworld off the original CD, and could play it - but only in software rendering, and at 640x480x16. But all animations, sounds, and interface worked flawless, at full speed. Many other games, though, didn't run at all (Mechcommander 2 (a Microsoft Title), Ground Control, Red Alert, Independence War), others worked but were unplayable because they did not read input devices correctly (Freespace, Freesapce 2). Other titles ran, but were so old that their usability was null (Tribes, Starsiege) because their infrastructure did no longer exists - not a fault of CO, though.
As a general rule of thumb, compatibility increases when the software is younger and starts using Windows-based standard libraries. This continues up to a certain point where the performance trade-off with the WINE-layer (10%-20%) makes the game unplayable. Most productivity software (MS Office, Quicken, Visio) not only runs really well, but because it is officially supported by CodeWeavers, installation is quick and painless. For many other titles that are not officially supported, there are community-provided installers that help you navigate some pitfalls during installation and that makes using CO much, much easier.
If you are using Steam, though, CrossOver is a great tool. Many old games work well when downloaded into a steam bottle (they will install into the same 'Bottle' as steam), and this way you gain access to many classic game titles that are otherwise unavailable on the Mac. Newer steam titles work, but the newest usually don't because of performance issues. Older titles usually work well, but may require you to install an obscure Windows library - without CO support I would not have been able to do so. that way, I was finally able to play 'Far Cry', 'Nexus: The Jupiter Incident' and 'Sins of a Solar Empire' on OSX.
So, is it worth it? If you have a legacy app and need to access or import windows data, then it is definitely worth a shot (you have a trial period to see if it works), and well worth the price if it works. It's much cheaper to than to spring for a full Windows emulation plus Windows, and you can switch between windows versions. For me, it's a great help, and the cost for service is negligible compared to what I would need to pay had I used a Windows PC and some Windows expert to keep it running. And if you want to play some classic titles that are available on Steam, it's definitely worth a try.