Forum Discussion

Dichotomy66's avatar
Dichotomy66
Brass Contributor
May 17, 2019
Solved

Finding closest match to average of a subset of column values

I have for example(actual is 200 rows) Col1              Col2   Col3 DBNUM       TYPE   EQUITY 1                 HHH      53 2                 MHH      45 3                  LMH      22 4      ...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    May 17, 2019

    Dichotomy66 ,

    As for the first question I'd use AGGREGATE() instead of MIN() to filter only records with code (HHH)

    =INDEX(MAIN[DBNUM],
   MATCH(1,
         (MAIN[TYPE]="HHH")*
         (ABS((AVERAGEIF(MAIN[TYPE],"HHH",MAIN[EQUITY])-MAIN[EQUITY]))=
               AGGREGATE(15,6,1/(MAIN[TYPE]="HHH")*(ABS(AVERAGEIF(MAIN[TYPE],"HHH",MAIN[EQUITY])- MAIN[EQUITY])),1)),
0))
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 17, 2019

Dichotomy66 ,

As for the first question I'd use AGGREGATE() instead of MIN() to filter only records with code (HHH)

=INDEX(MAIN[DBNUM],
   MATCH(1,
         (MAIN[TYPE]="HHH")*
         (ABS((AVERAGEIF(MAIN[TYPE],"HHH",MAIN[EQUITY])-MAIN[EQUITY]))=
               AGGREGATE(15,6,1/(MAIN[TYPE]="HHH")*(ABS(AVERAGEIF(MAIN[TYPE],"HHH",MAIN[EQUITY])- MAIN[EQUITY])),1)),
0))
Dichotomy66's avatar
Dichotomy66
Brass Contributor
May 17, 2019

SergeiBaklanThanks Sergei, you are the Aggregate King! I suspected it was the solution but I still struggle with using it