Pushing Numbers a Little Harder – Good Results
This morning I was updating one of the Numbers spreadsheets I had and was trying to push the envelope a little more and see what tools Numbers really had for - well... crunching numbers. I had some data that I wanted to have massaged a little more automatically, as opposed to the brute-force method that I had been using for the past year - since I moved the data from Mesa to Numbers.
The first thing was to have a better calculation of the YTD comparison of monthly numbers. The issue was that I only wanted to look at those months I had data for this year in the previous year. Making it as apples to apples a comparison as possible. The trick there was that the comparison needed to be a sum of the data, so it was possible to make a new column with the IF() function, but it was done even better with Numbers' SUMIF() function.
By simply making the test range this year, and the comparison ">0" and the sum range the previous year's data, I get the sum for the previous year's data for only those months that I have data for this year. Excellent! One thing done automatically that I needed to do.
The next thing was to have the year-on-year monthly changes automatically computed without messing up the sums or averages by putting a '0' in the cell. This was pretty easily done with an IF() call where the conditional was the value for this month of this year being great than zero, and the true clause was the percent change. The false clause was simply the empty string, and that worked out pretty well.
When I was done with the process I had a workbook that looked the same, but acted entirely differently, and much more like a cool, complex, spreadsheet should operate. Now I put in the raw data and shazam! the complete workbook gets all the data it needs. Very cool.
Yes, I realize this isn't that big a deal, but the little I do with spreadsheets is very much brute-force and the steps to find out these functions and use them well was an interesting exercise.