Search code examples
excelexcel-formulastatisticsmean

Excel, how to have grouped TRIMMEAN

Hi i have dynamic data updated from remote every day of that type

state city id number
s1 c1 i1 45
s1 c1 i2 2
s1 c2 i3 12
s2 c1 i4 23
s2 c4 i5 45

i need a result set with this structure

state city avg

i try with pivot but TRIMMEAN() is not available as group function

i cannot add column so source data since is imported from external web sites and row number change each time

result set will be source for other sheet with other calculation so every solution must have static column and be able to manage dynamic rows generated from input

Solution

  • Perhaps using GROUPBY() or PIVOTBY() you can accomplish the output returned with TRIMMEAN() function with a LAMBDA() construct to iterate the function, here are the solutions you can follow, if you are presently using MS365 with the Beta Channel Enabled.

    • Using GROUPBY()

    enter image description here

    • Formula used in cell F2

    =GROUPBY(A2:B6,D2:D6,LAMBDA(x,TRIMMEAN(x,0.2)),0,0)

    • Or, Using PIVOTBY()

    =PIVOTBY(A2:B6,,D2:D6,LAMBDA(x,TRIMMEAN(x,0.2)),0,0)

    • MSFT Documentations --> TRIMMEAN

    Also, to get the output generated dynamically even when new data is added to the source I had prefer using Structured References aka Tables that way, it does not iterates unwanted blank rows for data manipulations, however, if one is not fond of Structured References may use as follows.

    enter image description here

    =LET(
     _lastRow, MATCH(2^2,1/(A:A<>"")),
     _data, A2:INDEX(A:D,_lastRow,),
     GROUPBY(TAKE(_data,,2),TAKE(_data,,-1),LAMBDA(x,TRIMMEAN(x,0.2)),0,0))

    • Using Tables its more cleaner and simpler.

    enter image description here

    =GROUPBY(TAKE(SourceTbl,,2),TAKE(SourceTbl,,-1),LAMBDA(x,TRIMMEAN(x,0.2)),0,0)