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))
Dichotomy66's avatar
Dichotomy66
Brass Contributor
May 17, 2019

Another option that would be nice is

to have it return say 3 Random DBNUM that are of the specific type (HHH for ex)

that also either include or exclude values from up to 6 other columns

Col 4            Col5                   Col6

PAIRED        SUITED               ACE

TRUE                                      TRUE

                     TRUE

                      TRUE                TRUE

TRUE              TRUE                TRUE

                                                TRUE