Formulas are not being updated on row filter

Posted by: davide.vago on 30 August 2019, 12:54 am EST

  • Posted 30 August 2019, 12:54 am EST

    Good morning/evening

    Given a set of data within the spreadsheet instance, when the filters area applied programmatically any row which contains formulas don't update the value based on the visibility of the rows.

    Let's say you apply a filter using the filter() method to a list of rows 0 - 10 and the rows 3-5 disappear, I'm expecting to see formulas like AVERAGE or MIN/MAX to be updated automatically in case the filtered out rows are not visible.

    Do you cover this case, if so, what's the best solution?
    Thanks in advance.
  • Marked as Answer

    Replied 1 September 2019, 6:37 pm EST

    Hi Davide,

    This is the intended design behavior, i.e. hidden rows are included in the formula calculation. This behavior is the same is as MS Excel. If you would like to ignore hidden rows then you may use Subtotal or Aggregate formulas which allows us to ignore the hidden rows/values. Please refer to the following resources:
    • Subtotal function: https://exceljet.net/excel-functions/excel-subtotal-function
    • Aggregate function: https://exceljet.net/excel-functions/excel-aggregate-function

    Regards
  • Replied 5 September 2019, 1:52 am EST

    Thanks, this is perfectly working and it solved the issue
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels