SOLVED

Finding closest match to average of a subset of column values

Brass Contributor

I have for example(actual is 200 rows)

Col1              Col2   Col3

DBNUM       TYPE   EQUITY

1                 HHH      53

2                 MHH      45

3                  LMH      22

4                  HHH      40

5                   LLL        12

 

I want to return the DBNUM that s the closest match to the avg equity of HHH and is also TYPE HHH

=INDEX(MAIN[DBNUM],MATCH(TRUE,(ABS((AVERAGEIF(MAIN[TYPE],"HHH",MAIN[EQUITY])-MAIN[EQUITY])))=MIN(ABS((AVERAGEIF(MAIN[TYPE],"HHH",MAIN[EQUITY])-MAIN[EQUITY]))),0))

 

It is giving me a match to the average but it is a type MHH. I tried adding in (MAIN[TYPE]="HHH")*((ABS((AVerageif….   but it returns error

=INDEX(MAIN[DBNUM],MATCH(TRUE,(MAIN[TYPE]="HHH")*((ABS((AVERAGEIF(MAIN[TYPE],"HHH",MAIN[EQUITY])-MAIN[EQUITY])))=MIN(ABS((AVERAGEIF(MAIN[TYPE],"HHH",MAIN[EQUITY])-MAIN[EQUITY])))),0))

4 Replies

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

best response confirmed by Dichotomy66 (Brass Contributor)
Solution

@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))

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

@Sergei BaklanAny thoughts on the Random idea? 


1 best response

Accepted Solutions
best response confirmed by Dichotomy66 (Brass Contributor)
Solution

@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))

View solution in original post