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!
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()
=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