Advanced Formula help

Help
1

What I’m trying to do

I have a 3x7 table. Rows 1 and 2 are data; row 3 is a difference.
This formula works:

<!-- TBLFM: @>$2..@>$>=(@2-@3);%2f -->

But now I want column 6 to be a formula based on column 2, and I can’t make that formula parse.

Things I have tried

<!-- TBLFM: @I$6..@>$6=(0.38*$2);%2f -->

I also tried putting the 0.38 in a table cell and referencing it explicitly in the formula instead of as a raw number, and that didn’t change anything.

In another table, I have this formula, which works fine:

<!-- TBLFM: @I$4..@>$4=($2*$3);%.2f -->

and I really don’t see how the failing one is different, except for the range and the multiplier.

What am I missing?

2

Not sure what you’re missing, but we’re missing the table to be able to reproduce and test any improvements to your formulas.

3

With some random data, these formulas seem to work nicely:

<!-- TBLFM: @>$2..@>$>=(@1-@2);%.2f -->
<!-- TBLFM: @I$6..@>$6=(0.38*$2);%.2f -->

I’m not entirely sure if there are any differences, but I had to remove the “;%.2f” part, evaluate the formulas, and then add them back in again, re-evaluate, and it worked…

4

The actual table has my personal financial data in it, but the basic table is attempting to assess the tax differences in two scenarios.

In both scenarios, assume I have held 100 shares of a restricted stock for >1 year and then the company IPOs and it is released to me with a specific valuation. 38 shares are withheld for taxes, and I sell the remaining 62 shares for a $7.50 each for a net income of $465.

In row 2, the stock is valued at $10 when I receive it. In row 3, the valuation is $5.

(Column 2 is the taxable income I recognize at vesting time; Column 3 is my net cost basis of the 62 remaining shares, column 4 is the proceeds I get from selling those 62 shares at $7.50; column 5 is the long-term capital gain (or loss) for selling the stock with the given cost basis. Column 6 is the regular income tax liability for the release/vest event, and column 7 is the LTCG (or loss) for the sale of the remaining 62 shares.)

|  Valuation | Taxable Reg Income | Net Cost Basis | Proceeds |    LTCG | Inc Tax | CG Tax |
| ----------:| ------------------:| --------------:| --------:| -------:| -------:| ------:|
|      10.00 |            1000.00 |         620.00 |   465.00 | -155.00 |         |        |
|       5.00 |             500.00 |         310.00 |   465.00 |  155.00 |         |        |
| Difference |             500.00 |         310.00 |     0.00 | -310.00 |    0.00 |   0.00 |
<!-- TBLFM: @1$6..@2$6=(0.38*$2);%2f -->
<!-- TBLFM: @1$7..@2$7=(0.15*$5);%2f -->
<!-- TBLFM: @>$2..@>$>=(@2-@3);%.2f -->

If I delete the first two formulas, the third one does work to make row 4 be row 2 minus row 3. But the presence of either of those breaks the table.

5

wait, wut?

6

That worked for me also. Good to know! Thanks!

7

but now evaluating the formulas hitting the tab key puts an extra separator row in the table and breaks the format.

8

I really don’t know why it works, but it did work. And now when I copied your test table, it keeps on insisting to add a few columns. So I reckon the plugin is not the most stable there is. Sadly, it’s the only one I know of allowing for table formulas…

1 Like
9

It seems like it’s counting the header row as a row, so you need to adjust the formulas to the following:

<!-- TBLFM: @2$6..@3$6=(0.38*$2);%.2f -->
<!-- TBLFM: @>$2..@>$>=(@2-@3);%.2f -->
<!-- TBLFM: @2$7..@3$7=(0.15*$5);%.2f -->

Then it allows recalculation, and tabbing…

10

OK, the real problem is that I misunderstood the row addresses. @1 is the very first row in the table (the header row). @I is the first row after the separator, so @2 and @3 are the real rows I want here. (I had thought @3 and @4, but apparently the separator row doesn’t occupy a row number.)

The correct formulas are therefore

<!-- TBLFM: @2$6..@3$6=(0.38*$2);%.2f -->
<!-- TBLFM: @2$7..@3$7=(0.15*$5);%.2f -->
<!-- TBLFM: @>$2..@>$>=(@2-@3);%.2f -->

and they work and don’t mess up the table.

Thanks for your help!

11

No, or almost… See my previous response. The separator row doesn’t count , so you want to use row 2 and row 3. Row 4 is the “difference” row…

1 Like
12

Wow, we’re answering in the “mouth” of each other. But I hope we can agree in the end that the separator row doesn’t count, so the first row after the separator is referenced as either @I or @2, and this is most likely the cause of the some of the confusion earlier on this thread.

1 Like
13

Yes indeed!

14

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.