Advanced Formula help

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..@>$>=(@[email protected]);%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?

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

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

<!-- TBLFM: @>$2..@>$>=(@[email protected]);%.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…

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: @[email protected]$6=(0.38*$2);%2f -->
<!-- TBLFM: @[email protected]$7=(0.15*$5);%2f -->
<!-- TBLFM: @>$2..@>$>=(@[email protected]);%.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.

wait, wut?

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

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

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

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

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

Then it allows recalculation, and tabbing…

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: @[email protected]$6=(0.38*$2);%.2f -->
<!-- TBLFM: @[email protected]$7=(0.15*$5);%.2f -->
<!-- TBLFM: @>$2..@>$>=(@[email protected]);%.2f -->

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

Thanks for your help!

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

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

Yes indeed!

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