TRIMMEAN If

I want to apply TRIMMEAN to a range, but only if cells in another range is positive. This formula will reside in RESULTS C11.

I've seen several examples like this

Please Login or Register  to view this content.

But I cannot get it to work with what I have. Here's a TRIMMEAN formula that works by itself in RESULTS C11...

Please Login or Register  to view this content.

I want to condition the following...

Please Login or Register  to view this content.

TRIMMEAN(RANGE,%), if range is an array (created from a condition formula), it required confirmation by Ctrl-shift-enter (not only Enter)

=TRIMMEAN(IF(Table1[G/L $]>0,Table1[G/L],""),0.05)

What TRIMMEAN were you using to get .13? With Enter or Ctrl-shift-enter?
=TRIMMEAN(IF(Table1[G/L $]>0,Table1[G/L],""),0.05)
I got .245943, with Ctrl-shift-enter

Dismiss that calculation. As a test instead, I manually extracted the positive numbers into a new sheet and ran =TRIMMEAN(A1:A165,0.05). I get .247446. This number and yours should be the same now, shouldn't they?

Please educate me on this... I'm not understanding the array aspect. Why must this be entered as an array... the data source(s) are ranges?

Edit: See if maybe I understand now... Although the original data was in a range, it became an array when pulling out only the positive numbers?

I tried to remove 0 and blank cells, extracted to other sheet "test", then tested both formula, I got same result. See attachment

Originally Posted by cableghost

As a test instead, I manually extracted the positive numbers into a new sheet and ran =TRIMMEAN(A1:A165,0.05). I get .247446. This number and yours should be the same now, shouldn't they?

I guess that you did not delete all rows with blank? Excel treats blank cell as zero value.

a range: you can see it is visible in sheet: i.e, A1:A165
but an array: it is created from a formula, that you cannot see it: =ID(A1:A165>0,A1:A165,"") is visible. It require to go through each cell, do math: A1>0 then A1, A2=0 then blank,...,until A165
Some funtion does not accept array, i.e, COUNTIF(RANGE,..), but not COUNTIF(IF(...),...), similar to SUMIF...
Some work with array, but require Ctrl-Shift-Enter, i.e, TRIMMEAN...