2

The latest version of Numbers has a great feature called Smart Categories that are kind of like Excel's pivot tables. I'm using it to group a table by a label and then sum up spending and revenue numbers.

The next thing I'd like to do is calculate the ratio between these two, i.e. a new column that has the value revenue / spending. How do I do this? If I create a new column, the only options are to use built-in summary functions (see below) - I can't enter my own formula.

Example of smart categories:

example of smart categories

Options for "Ratio" column:

options for Ratio column

1

While it's not (currently) possible to place custom values in the column you desire, it is possible to refer to the operands elsewhere, such as by using a secondary table to refer to the cells in question. To make a formula point to a certain category’s subtotal, hold ⌥ option and click on the desired cell.

Unfortunately, this method does require the categories to be entered manually, so it may not be suitable for all situations.

Here is a demonstration using your example table, with a secondary table holding ratio values for each of the categories:

Table 1::$Revenue $test (Subtotal)÷Table 1::$Spend $test (Subtotal)

| improve this answer | |
0

It looks like Numbers doesn't yet support custom formulas here. You can reference those cells from other locations, but you can't actually use them for grouping unfortunately.

| improve this answer | |

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .