You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

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

I am currently using a 'freeform' Number's spreadsheet to track buying/selling, dividends, growth etc for a portfolio of investments. Am now considering setting up a new spreadsheet for same using the 'categories' feature within numbers. I have noted that in each sub-total/total cell address (for say a number of stocks) the options to calculate a result are limited to:-

If I want to use a custom formula (eg. to calculate a weighted average rather that just the "average" on offer in the 'limited' selection above, is this possible? If not, is there a workaround? Also, it would be good if the above 'standard' list of options included an item for "use custom formula" which, when selected, would enable me to enter (freeform) the customised formula into the relevant sub-total/total cell address. If I am limited to the above, somewhat 'scant' options for sub-total/total cell addresses, then I cannot see how I could utilise the 'categories' feature in Numbers; and will go back to using my 'Freeform' spreadsheet. Any comments here would be appreciated. Thanks - Gavin.


Posted on Oct 19, 2022 9:54 PM

Reply
Question marked as Top-ranking reply

Posted on Oct 21, 2022 10:03 PM

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



Similar questions

6 replies
Question marked as Top-ranking reply

Oct 21, 2022 10:03 PM in response to SGIII

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



Oct 20, 2022 6:02 AM in response to DreamBody

It's not clear exactly what you are trying to do, but a function commonly used to calculate a weighted average is SUMPRODUCT.


I don't think using Categories or Pivot Tables, both highly useful in summarizing date, especially Pivot Tables, will add much to using SUMPRODUCT, which is simple and straightforward.


A look at what you have in your table will help, so you might consider posting a screenshot.


SG

Oct 20, 2022 6:44 PM in response to Barry

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

Oct 19, 2022 11:49 PM in response to DreamBody

Calculating weighted averages has been possible since prior to the introduction of Numbers is late 2007. Can't give you a specific example as you've not provided any information regarding the categories, the range of values in each category, and the weight each category should have in the Average.


Regards,

Barry

Oct 20, 2022 7:00 PM in response to DreamBody

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

Oct 21, 2022 6:33 AM in response to DreamBody

I don't see any formulas in your screenshots. It's almost impossible to understand what is going on without seeing specifics of how you are calculating CAGR and weighted average


As you know portfolio calculations get complicated. Typically I would use software specifically designed to help track and manage portfolios. What you are hoping to gain by using Categories for this purpose?


SG


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

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.