Use min and Max excluding top and bottom values

I have a table with several percentage values corresponding to concept, strategic etc.

Now, the user can select a value from a table.

After this, I want to show min,max and average of the filtered value (NOT ALL FILTERED VALUE BUT VALUE FROM MIDDLE, EXCLUDING TOP 25% and lower 25%)

I tried to do it with filter (as shown) in attached, and have shown sample data for two typical cases.

I was able to use trimmean to get as an array to get the average but NOT the maximum and minimum.

Can it be done using any formula sequence or array formula, possibly without VBA for minimum and maximum too

Thanks in advance.

Make a table of your data.

below 0,00%
min 14,00% Yes
max 20,00% yes
above 20,01%

D2 =VLOOKUP($C10,$J$1:$K$14,2,1)

See the attached file.

I think it shouldn't goes this way. Cut off 25% of bottom results and 25% of top results as result not a result value. So you can't set criteria 14% and 20% to cut off (as I understand).
I've prepared something in VBA like this:

Put into standard module:

Sub MyCalc()
Dim cell As Range
Dim temp() As Variant
Dim MyVal2() As Variant
Dim i, j As Long
i = 0
j = 0
For Each cell In [B4:B150]
    If cell.Value = [D1].Value Then
        ReDim Preserve temp(i)
        temp(i) = cell.Offset(0, 1).Value
        i = i + 1
    End If
Next cell
For i = ((UBound(temp) + 1) * 0.25) To ((UBound(temp) + 1) * 0.75 - 1)
        ReDim Preserve MyVal2(j)
        MyVal2(j) = temp(i)
        j = j + 1
Next i
[D2].Value = Application.WorksheetFunction.Average(MyVal2)
[E2].Value = Application.WorksheetFunction.Min(MyVal2)
[F2].Value = Application.WorksheetFunction.Max(MyVal2)
End Sub

and into code of Sheet1 or Sheet2 (check attached file):

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [D1]) Is Nothing Then
    Range("$A$3:$C$121").AutoFilter Field:=2, Criteria1:=[D1].Value
    Call MyCalc
End If
End Sub

imho works correctly.

Please try at
E4
=AVERAGEIFS(C4:C121,B4:B121,D1,C4:C121,">="&F2,C4:C121,"<="&G2)

F4
=QUARTILE.INC(IF(B4:B121=D1,C4:C121),1)

G4
=QUARTILE.INC(IF(B4:B121=D1,C4:C121),3)

Ctrl+Shift+Enter for E4 and G4

Thanks all

I will check it and will let you know.

Thanks all.

But, the solutions didn't solve the problem to get the desired result.

To make things clear, I have revised the data. Now the data against post tender contains 1% to 10%

I want to trim off 40% of total data (out of 10), so 2 from the top and 2 from the bottom of the values.
So, if 2 extreme data are taken out from top & bottom it gives values ranging from 3% to 8%.

Average of this range is 6%, minimum is 3% and max 8%. Trim mean does gives the average, but need for a formula to give min & max.

Alternatively, I managed to get formulas on rows to get the desired outcome (might not be the best way to do it), but would prefer a single formula to get it. (Possibly an array formula) The attached sheet has cell highlighted in yellow to give the result as a formula - need to make that formula to an array formula.

This is trim 50% in Post #1

Originally Posted by saravnepali

EXCLUDING TOP 25% and lower 25%)

Post #6

Originally Posted by saravnepali

I want to trim off 40%

Last try
Min
=PERCENTILE.INC(IF(B4:B15=D1,C4:C15),0.2)
or
=INDEX(C4:C15,MATCH(1,($C$4:$C$15>=PERCENTILE.INC(IF(B4:B15=D1,C4:C15),0.2))*($B$4:$B$15=D1),))

Max
=PERCENTILE.INC(IF(B4:B15=D1,C4:C15),0.8)
or
=LOOKUP(2,1/(C4:C15<=PERCENTILE.INC(IF(B4:B15=D1,C4:C15),0.8))/(B4:B15=D1),C4:C15)