Shortcode Filter Then Average Of Results

  • Resolved sison2466

    (@sison2466)


    2 years, 2 months ago

    I have a table of cities within countries with population values.
    Example:
    City A, Country A, 10,000
    City B, Country A, 5,000
    City 1, Country B, 10,000

    In my footer, I have =average(C1:C3)
    This works if I use the whole table and I see the average of all the countries.

    However, If I want to use a shortcode to filter only for Country A, then give me the average population, I get a !ERROR! Circle Reference

    I installed the Parse Shortcodes before Formulas.

    My shortcode looks like

    [table id=73 filter=”Country A” filter_columns=”2″ / shortcodes_before_formulas=true / ]

    Two questions:

    1) Is there an easier way to dynamically calculate an average of table results?
    2) If no, what am I doing wrong in my shortcode?

    Your help is appreciated.

    Cheers,

    Marco

Viewing 1 replies (of 1 total)
  • Plugin Author TobiasBg

    (@tobiasbg)

    Hi,

    thanks for your post, and sorry for the trouble.

    The problem essentially is that your formula can not automatically adapt to rows being missing after the filtering. Essentially, in your example, after the filtering, your footer cell with the formula will actually be cell C2 or C3 — which leads to that circle reference.

    What you would need is an automatic generation of cell references, but the formula evaluation library in TablePress 1.14 can not do that. However, the upcoming TablePress 2.0 can ?? Please see the announcement and download of a beta version here: https://tablepress.org/8-million-downloads-tablepress-2-0/

    Then, you could use a formula like

    =AVERAGE( C1:ADDRESS( ROW()-1, 3 ) )

    The ADDRESS() function generates a reference like C3, from a row number (which will be ROW()-1 if you put this formula in the table footer) and a column number (where 3 is for C).

    Your Shortcode can then be

    [table id=73 filter="Country A" filter_columns="2" /]

    (or rather, add

    filter="Country A" filter_columns="2"

    to the “Configuration parameters” field in the “Advanced” section of the TablePress table block’s Settings sidebar, after inserting the block into your page.

    (The Parse Shortcodes before Formulas Extension does not help here, as that’s about Shortcodes inside the table cells, which does not apply here.)

    Regards,
    Tobias

Viewing 1 replies (of 1 total)
  • The topic ‘Shortcode Filter Then Average Of Results’ is closed to new replies.