Forum Discussion
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))
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))
4 Replies
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))SergeiBaklanAny thoughts on the Random idea?
SergeiBaklanThanks Sergei, you are the Aggregate King! I suspected it was the solution but I still struggle with using it
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
