Advanced Tables formula help

I have this table and I want to add the counts in each row to arrive at a total for each denomionation.

Denomination Them Me Total Count
.25 100 100
$1 200 200 400
$5 400 300 700
$25 100 100 200
$100 50 50 100
$500 25 50 75
$1000 25 50 75
------------- ------- ------- 0
Totals 800 850 1650
table looks like above
formula as I currently have it:

<!-- TBLFM: $>=sum($2..$-1) -->

The formula does add each row and updates the value for each row in the totals column. The issues is that it also tries to add the last two rows (one of which is a divider) which I do not want. How do I ammend the formula so that it adds all the rows with the exception of the last two?

1 Like

Hi @odin , I reread your question, and found my previous answer missed by a mile, but I’ve now read the documentation, and found a solution for you:

<!-- TBLFM: @I$>..@8$>=sum($2..$-1) -->

This formula limits the summation to a given set of rows, namely from the first after the divider, @I, and until the 8th row, @8. The rest of the formula is your original formula.

I tried doing @-2, but it then complains about not wanting relative references, so I had to specify the row directly. But as long as you remember to change the @8 if you add/remove denominations, you should be fine.

In my test case, I also added the following below the other formula comment to sum the columns:

<!-- TBLFM: @>$2..@>$3=sum(@I..@-2) -->

Hope this helps,
Holroy

A bit late to the party, but i’ extremely lazy for this kind of stuff. I use advance table everday. But use gpt for the computation.

Using Advanced Table with AVA (perfect for lazy people)

Note: i’m a co-author of ava

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