SOLVED
Home

Finding closest match to average of a subset of column values

%3CLINGO-SUB%20id%3D%22lingo-sub-583688%22%20slang%3D%22en-US%22%3EFinding%20closest%20match%20to%20average%20of%20a%20subset%20of%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-583688%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20for%20example(actual%20is%20200%20rows)%3C%2FP%3E%3CP%3ECol1%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20Col2%20%26nbsp%3B%20Col3%3C%2FP%3E%3CP%3EDBNUM%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20TYPE%20%26nbsp%3B%20EQUITY%3C%2FP%3E%3CP%3E1%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20HHH%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%2053%3C%2FP%3E%3CP%3E2%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20MHH%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%2045%3C%2FP%3E%3CP%3E3%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20LMH%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%2022%3C%2FP%3E%3CP%3E4%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20HHH%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%2040%3C%2FP%3E%3CP%3E5%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20LLL%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%2012%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20return%20the%20DBNUM%20that%20s%20the%20closest%20match%20to%20the%20avg%20equity%20of%20HHH%20and%20is%20also%20TYPE%20HHH%3C%2FP%3E%3CP%3E%3DINDEX(MAIN%5BDBNUM%5D%2CMATCH(TRUE%2C(ABS((AVERAGEIF(MAIN%5BTYPE%5D%2C%22HHH%22%2CMAIN%5BEQUITY%5D)-MAIN%5BEQUITY%5D)))%3DMIN(ABS((AVERAGEIF(MAIN%5BTYPE%5D%2C%22HHH%22%2CMAIN%5BEQUITY%5D)-MAIN%5BEQUITY%5D)))%2C0))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20giving%20me%20a%20match%20to%20the%20average%20but%20it%20is%20a%20type%20MHH.%20I%20tried%20adding%20in%20(MAIN%5BTYPE%5D%3D%22HHH%22)*((ABS((AVerageif%E2%80%A6.%20%26nbsp%3B%20but%20it%20returns%20error%3C%2FP%3E%3CP%3E%3DINDEX(MAIN%5BDBNUM%5D%2CMATCH(TRUE%2C(MAIN%5BTYPE%5D%3D%22HHH%22)*((ABS((AVERAGEIF(MAIN%5BTYPE%5D%2C%22HHH%22%2CMAIN%5BEQUITY%5D)-MAIN%5BEQUITY%5D)))%3DMIN(ABS((AVERAGEIF(MAIN%5BTYPE%5D%2C%22HHH%22%2CMAIN%5BEQUITY%5D)-MAIN%5BEQUITY%5D))))%2C0))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-583688%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-583736%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20closest%20match%20to%20average%20of%20a%20subset%20of%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-583736%22%20slang%3D%22en-US%22%3E%3CP%3EAnother%20option%20that%20would%20be%20nice%20is%3C%2FP%3E%3CP%3Eto%20have%20it%20return%20say%203%20Random%20DBNUM%20that%20are%20of%20the%20specific%20type%20(HHH%20for%20ex)%3C%2FP%3E%3CP%3Ethat%20also%20either%20include%20or%20exclude%20values%20from%20up%20to%206%20other%20columns%3C%2FP%3E%3CP%3ECol%204%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20Col5%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Col6%3C%2FP%3E%3CP%3E%3CU%3EPAIRED%3C%2FU%3E%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%3CU%3ESUITED%3C%2FU%3E%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3CU%3E%20ACE%3C%2FU%3E%3C%2FP%3E%3CP%3ETRUE%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20TRUE%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20TRUE%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20TRUE%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20TRUE%3C%2FP%3E%3CP%3ETRUE%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20TRUE%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20TRUE%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20TRUE%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-583983%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20closest%20match%20to%20average%20of%20a%20subset%20of%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-583983%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F322032%22%20target%3D%22_blank%22%3E%40Dichotomy66%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3EAs%20for%20the%20first%20question%20I'd%20use%20AGGREGATE()%20instead%20of%20MIN()%20to%20filter%20only%20records%20with%20code%20(HHH)%3C%2FP%3E%0A%3CPRE%3E%3DINDEX(MAIN%5BDBNUM%5D%2C%0A%20%20%20MATCH(1%2C%0A%20%20%20%20%20%20%20%20%20(MAIN%5BTYPE%5D%3D%22HHH%22)*%0A%20%20%20%20%20%20%20%20%20(ABS((AVERAGEIF(MAIN%5BTYPE%5D%2C%22HHH%22%2CMAIN%5BEQUITY%5D)-MAIN%5BEQUITY%5D))%3D%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20AGGREGATE(15%2C6%2C1%2F(MAIN%5BTYPE%5D%3D%22HHH%22)*(ABS(AVERAGEIF(MAIN%5BTYPE%5D%2C%22HHH%22%2CMAIN%5BEQUITY%5D)-%20MAIN%5BEQUITY%5D))%2C1))%2C%0A0))%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-584307%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20closest%20match%20to%20average%20of%20a%20subset%20of%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-584307%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3EThanks%20Sergei%2C%20you%20are%20the%20Aggregate%20King!%20I%20suspected%20it%20was%20the%20solution%20but%20I%20still%20struggle%20with%20using%20it%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-584308%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20closest%20match%20to%20average%20of%20a%20subset%20of%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-584308%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3EAny%20thoughts%20on%20the%20Random%20idea%3F%26nbsp%3B%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3C%2FLINGO-BODY%3E
Dichotomy66
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

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? 


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