SOLVED

Contributor

# 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                  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

# Re: Finding closest match to average of a subset of column values

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 (Contributor)
Solution

# Re: Finding closest match to average of a subset of column values

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

# Re: Finding closest match to average of a subset of column values

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

# Re: Finding closest match to average of a subset of column values

@Sergei BaklanAny thoughts on the Random idea?