Appleworks vs Numbers Spreadsheet Functions

In my last post about converting AppleWorks documents, I mentioned that I hadn’t found a comparison of AW and Numbers spreadsheet functions and how they differed. Well, I decided to take on the task myself. It turned out to be one of those been there, done that, and won’t ever do it again projects, but it will help me convert the rest of my collection of AW spreadsheets, and a few other CompuNerds may also find it useful. If you click on the image below, you will download a .pdf file which lists and compares all of the functions in both applications. The ones that probably won’t cause any problems are in green, the ones that are likely to not work correctly after opening the AW spreadsheet in Numbers are in red, and there are a few gray areas as well.

Numbers has a much larger and more powerful set of functions than Appleworks, and there is a corresponding function in Numbers for most AW functions. However, AW has some unique functions, and some of the similar functions in the two applications don’t work quite the same way. The result is that most simple AW spreadsheets are translated into Numbers correctly just by dragging and dropping the AW spreadsheet, but many even moderately complex ones are not.

How do you tell?

1. For some problems, Numbers will pop up a “There are problems” alert box when it opens the AW spreadsheet.

2. The cells in which there are problems will usually be marked with a little blue triangle in the lower left. Clicking on the cell will give you some information about the problem.

3. Test it! Even though the conversion appears to have been done correctly, compare the numbers in the original AW spreadsheet with the Numbers spreadsheet. Change a few corresponding values in the two spreadsheets and make sure they both get the same new result.

Numbers has a very annoying behavior when it finds a function or formula that is not valid. It substitutes the last calculated value. That means the initial numbers in the converted spreadsheet will probably look right, but a new calculation will fail completely. Even more annoying, you have to go back to the original AW spreadsheet to find out what the offending formula or function was so you can figure out how to change it. This is a really good reason not to upgrade to Lion, which won’t run AppleWorks, until after you have successfully converted all of your spreadsheets.

How do you fix problems?

Well, you are just going to have to figure out how to accomplish the same task with Numbers functions and formulae. The good news is that Numbers has many more functions, and some of them can accomplish in one step what took several functions in AW. I have added some notes to the .pdf file comparing functions which may help.

For more information on exactly how a given function works in either AW or Numbers, go to Help in the respective top menu.

Disclaimer

I’ve tested most of these functions, but not all. Some of them I never use. There will probably be some surprises: Functions that should work don’t (maybe depending on the value of the arguments), and (probably rare) a function you don’t expect to convert correctly will. Good luck!