Search code examples
averagelibreofficelibreoffice-calcsubtotalsumproduct

Calculate the average of a filtered list/column with a condition (libre office calc)

I'm trying to calculate the average of a filtered list (J23:J1026), but with a condition.

The condition is to calculate the average of the values < 0.

I was able to get the count of the values < 0 with the following formula in cell D19:

=SUMPRODUCT(SUBTOTAL(3;OFFSET(J23;ROW(J23:J1026)-ROW(J23);0));(J23:J1026<0)+0)

But I am not able to calculate the average of the values < 0.

The result should update if the list is filtered in another column.

Does anybody know what I am missing?

Thank you very much in advance!

enter image description here

Solution

  • Edit: Formula suggested is related with Excel Tags which was earlier posted in OP(Original Post - Please see edits of the post), however OP(Original Poster) later changed the tags.

    Try using MAP() function with SUBTOTAL()

    enter image description here

    =LET(x, J23:J32, AVERAGE(TOCOL(x/((MAP(x,LAMBDA(y,SUBTOTAL(103,y))))*x<0),2)))

    Not sure about Libre Office Calc as per OP since LET(), MAP() and LAMBDA() are not available functions of the same, hence could try this way as well:

    =AVERAGE(IFERROR(J23:J32/(SUBTOTAL(103,OFFSET(J23:J32,ROW(J23:J32)-MIN(ROW(J23:J32)),,1))*(J23:J32<0)),""))

    Since I am not familiar with Libre Office Calc hence as per the comments of OP, I am borrowing the idea that the given formula in above will work when it is committed with CTRL+SHIFT+ENTER