Using categories in a numbers spreadsheet - formulas available for sub-total and total cells

Hi SG

My apologies for not providing you with the relevant info to date. Please also note that I got the row numbering sequence wrong in my previous messages. The first row with data is row#3 (not #1 as previously stated). The header rows take up #1 & #2.

The three total rows are # 6, 11 and 13. The crucial formulas on these rows are:-

In Cell H6:

(H3*I3)+(H4*I4)+(H5*I5) = 9.90 years

In Cell M6:

POWER (G6/E6),(1/H6))-1 = 5.86%

In Cell H11:

(H7*I7)+(H8*I8)+(H9*I9)+(H10*I10) = 7.29 yrs

In Cell M11:

POWER (G11/E11),(1/H11))-1 = 4.91%

In Cell H13:

(H3*J3)+(H4*J4)+(H5*J5)+(H7*J7)+(H8*J8)+(H9*J9)+(H10*J10) = 8.21 yrs

In Cell M13:

POWER (G13/E13),(1/H13))-1 = 5.25%

Yes I am aware that portfolio ROI calculations are complicated. I have in the past used a custom (Filemaker) database package but it turned out to be somewhat outdated and limited, and the developer was retiring and not interested in upgrading it. Also, many off-the shelf DB packages are for USA, not Australia - and therefore unsuitable for us. I have a 'beginners' knowledge of the Filemaker/relational DB. However this DB offering, as well as most others, are priced for developers who are, or intend, on-selling their creations. I am only doing this for personal use - not for sale. Hence, most DB's are too pricey for my purposes. If I were to take time to learn a new DB language, I would then have to design and build the DB. If I overlook something in design stage, then it takes more effort to re-design/build later on. Hence, I have stuck with using Numbers/spreadsheets, which I am familiar with - but this too can get quite ungainly as the spreadsheet(s) grow in size/complexity over time.

I am also currently looking at a new hybrid DB/spreadsheet app called "RAGIC". These people have developed a new DB app that is specifically targeted at spreadsheet users who do not want to go to time/expense etc of learning/using an appropriate DB software application. This is a work-in -progress (for me).

What am I hoping to gain by using Categories in Numbers? I have duplicated my main spreadsheet and am using it as a test for the Categories feature in Numbers. I like the way it allows me to drag and drop SOLD stocks/funds from the "Currently Held" area into the "Sold" area of the portfolio, without affecting formulas on the 'total' rows. I also like the ability (in Categories) of re-sorting data, changing categories etc, all without affecting formulas in 'total' rows. Currently, when we buy/sell stocks, I have to go into spreadsheet (at year's end) and alter formulas etc. Very time consuming and error-prone.

However, as much as I like these 'category' features, not being able to use a bigger selection of formulas (as we can in 'freeform' spreadsheeting) on the 'total' rows, is a major limitation.

Any comments/suggestions you have here would be appreciated.

Thanks Gavin

Hi Barry,

Thanks for your quick response. Yes I am aware that Numbers has a good array of formulas for use when doing what I call 'freeform' Numbers. In so doing I am able to insert whatever formula I wish (from the selection of formulas within Numbers) at any cell address on the sub-total/total rows within each table. However, when I decided to try using the 'category' feature within Numbers, I find my formula options are restricted to the 'drop-down' pallet shown in my first message on this subject (yesterday). As requested, I have prepared an table for you with sample data, as follows:-

As you can see from this table, we get entirely different results from using either 'freeform' vs 'categorised' Numbers tables.

I am quite attracted to using the 'category' feature in Numbers for the ease in which I can sort data and retain formulas in a constantly changing investment portfolio environment. However, it appears that when using the category feature, Numbers only provides a 'restricted' formula pallet - which is, as you can see by the above example data table, entirely inadequate. My question is - is it possible to use different formulas on the sub-total/total rows within a 'categorised' Numbers table, that the 'restricted' formulas on offer? If not, can you suggest a work-around solution? I trust that what I am now asking is clearer to you. Thanks Gavin

Hi Barry & SG,

I forgot to mention that what I use in my table(s) is in fact a 'time/cost weighted average' when calculating the 'average' number of years held for each investment group (i.e. Aust'n and Inter'nl). When calculating the compound annual growth rate % (CAGR) for each parcel of stock (i.e. by row) you can use the years held for each parcel, however when calculating CAGR for each group (i.e Aust'n OR Inter'nl) or for all groups (i.e. Aust + Inter'nl) you need to use the 'time/cost weighted average' to arrive at 'average' years held for each group. This figure is then used in the POWER formula to arrive at CAGR for each group.

Gavin