Formulas are not being updated on row filter

Formulas are not being updated on row filter

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

• 

  davide.vago

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  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.

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 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

• 

  davide.vago

  • Post Options:
  • Link
    Copy

  Posted 5 September 2019, 1:52 am EST

  Thanks, this is perfectly working and it solved the issue