Written by Darryn Lowe | 27 January 2009

Numbers would be have to be (for me) the most used application in the iWork suite. iWork was never of any real interest to me until they introduced Numbers and I've used it ever since...and its safe to say that I am a big fan of this version of Numbers.

As with the previous version, Numbers has the adjustable rows and columns feature that made it so unique. It also has that amazing Print View where you can adjust the spreadsheet to fit to the page that you're printing on. These features alone make Numbers an interesting offer, but Apple has also made some major changes to the way that Numbers works and a couple of changes that solve workarounds I had to do, making this version even more useful.

One of the things I liked so much about Numbers is the way calculations are presented. Take a look at the way iWork '08 showed text in the calculations box...

=SUM(Time Taken :: B2:H2)

Looks all right doesn't it? Yeah, I'll let it slide. Until that was that I saw '09's display...

"Wow, that is... really really boring" I hear you say. True it does seem that but the difference when using it is huge. Say you wanted to alter that formula. In the first example, you would have to either delete each character one at a time or select and delete parts. In the example from the new version, you simply place the cursor inside the bracket and press delete and the whole formula is gone. In terms of time saving you're talking microseconds vs seconds. That is BIG.

It does have a draw back though. Take this equation if you will...

=IF(D2="Onsite",((H2-G2)+(I2-H2)+(J2-I2))*60*24,(I2-H2)*60*24)

Here's where the drawback comes in. When you enter this by clicking one cell, it for some reason selects the part of the equation that already has that field. So for example the part of the equation that goes (H2-G2)+(I2-H2) when you click on H2 for the second time instead of adding another H2 into the equation it jumps to the H2 that is already in the equation.

However, if you hold down the Command button while clicking the fields this will allow you to edit the equation correctly. Mind you, that above equation was more cumbersome than it needed to be. All I really needed to do was...

=IF(D2="Onsite",(J2-G2)*60*24,(I2-H2)*60*24)

One thing that did irk me somewhat in '08 is clearly evident in the above equation. You'll notice that there is *60*24 at the end of each part of the equation. This comes down to an issue with '08 where it had no ability to calculate durations. You see in my spreadsheet G2 to J2 are formatted as Time fields so for example it would look like this...

G2	H2	I2	J2
13:30	13:35	13:40	13:45

However, because '08 can't actually handle durations it treats the answer to the above as seconds. The big problem there is that another cell takes the answer to this calculation and multiplies it by a dollar amount.

The answer to this calculation is quite a small number, so the answer for the dollar amount is very very low. So to compensate for this, one needs to multiply the answer by 60 (to give minutes) then by 24 (to give hours) in order for the amount to come out right. Numbers '09 fixes this with a new Cell Format called Duration.

As you can see by the picture on the left, you can set the Units to cover a wide timeline from weeks to milliseconds. In my case, I only need Hours and minutes, so on the slider you simply hold the ends and drag it to what you need. Very, very easy to perform what is really a complex idea. The Format drop down has a number of ways to display the end result. Here I've set it to "0:00" but there are two other options being "0h 0m" and "0 hours 0 minutes". To explain this the first and current selection will display the result as "0:15", the second will display "0h 15m" and the third will display "0 hours 15 minutes".

With Duration you no longer needed to make a complex equation that multiplies by 60 then by 24 to get the correct answer to work with, it already gives the correct answer. One thing to note though, ensure that when you are working with times that you have blank cells changed to 0:00 otherwise any equations where you need to calculate a range will cause an error:

There is one problem I've found with Duration which is kind of a reverse of the problem I've just mentioned. When trying to calculate say the percentage of time worked using a Duration field, the answer comes out as a duration not as a percentage. I haven't yet worked out how to get around this but I'll get there and make a note in the comments.

Top of the charts
Charting in Numbers has got even cooler when you work with Pages. Take the chart below. While this may actually seem like a static image (of course, here it is) it is actually a live chart in Pages, but from Numbers that you can interact with. Whatever I do in Numbers to alter this chart will show up here in this Pages document. You do need to save the spreadsheet before any changes show up in Pages though and you do have to perform a manual sync by clicking on the chart in Pages and clicking the iSync style icon. Making this automatic would be more useful but the fact it is there is still very nice, as opposed to the previous way which was to delete the previous chart then import the modified chart.

This linked data works very well except it seems when working with the Duration field as nothing shows up. This is really annoying but no doubt something that will be fixed soon hopefully. I have posted in the forum about this to see if anyone has any ideas.

One extremely handy feature is the Function browser. If like me you want to find out how to do something or you know what function to use but not how to use it then the Function Browser is where you need to go.

The browser is split into three windows, a grouping of the functions, the functions themselves and the description of the function with examples of how to use it. While this is in Excel it is still a nice touch and helps out a lot when trying to build your spreadsheets. There are now 250+ functions that you can use in Numbers which is a huge amount. It's still not as exhaustive as Excel but it should be more than enough for most users.

There is also a Formulas List option that brings up a box at the bottom of the screen that shows you a list of all the formulas you've used in your document. This is handy as it allows a quick and easy way of seeing where formulas are used and also allows you a quick and easy way of finding and replacing these formulas.

The last thing I want to talk about is Table Categories. For you to get a good understanding of how this works take a look at these screenshots...

This is how I grouped data in Numbers '08. Notice how I've created a worksheet and table for every day of the week? Now checkout Table Categories in Number '09...

What might be a bit difficult for you to see is that I've created categories for each day in the one table (grey bits). I can now expand or contract the data as you might be able to gather from this image with the two white sections. Here Monday and Thursday are expanded whereas Tuesday, Wednesday, Friday, Saturday, and Sunday are all contracted. This allows you to easily see the required data in a glance whereas before you'd have to go to various worksheets to get the same data.

From what I understand this is Number's version of Pivot Tables while not really being a pivot table so take that as you will. I'm not really sure about pivot tables as I've never really used them. All I can tell you though is that Table Categories are a very very handy feature because otherwise the data just gets really really out of hand.

Conclusion
I was limited in what I could achieve with the previous version of Numbers, but those limitations are now somewhat gone. That being said, there has been some new problems introduced. Numbers '09 is a very strong application and it has come a long way in its 2.0 release. It's painfully obvious that Apple has put a lot of hours into fixing Numbers and the result is truly a work of art as nerdy as that sounds when talking about a spreadsheet app.

Numbers is not quite there as a full replacement for Excel but it is unbelievably close. I feel that come version 3, Numbers would have sold itself to businesses looking to ditch Microsoft. As it stands for small businesses I really think Numbers would suite your budget. For personal users Numbers is the only spreadsheet you need.

 

 

FOOTNOTE: I worked out how to do the percentage thing on duration fields. Your equation should look something like this...

=STRIPDURATION(XY)/Z

XY = Cell

Z = how you want to get the percentage

E.g. =STRIPDURATION(Total Time)/35 will reference a cell that takes a SUM total of time spent on jobs during the week and divide by 35 which is the amount of hours a week I am paid excluding overtime.

Anyway, thank you Formula Browser for that little answer.