• 470K Members
• 6,263 Online
• 568K Conversations
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

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?

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
48 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies