SOLVED
Home

Help finding the biggest (absolute terms) movement in order

%3CLINGO-SUB%20id%3D%22lingo-sub-1064005%22%20slang%3D%22en-US%22%3EHelp%20finding%20the%20biggest%20(absolute%20terms)%20movement%20in%20order%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1064005%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20large%20set%20of%20data%20i%20update%20each%20day.%20I%20am%20hoping%20to%20fix%20a%20formula%20so%20i%20can%20find%20the%20large%20movement%20(either%20positive%20or%20negative)%20each%20day.%20I%20am%20looking%20to%20find%20the%20top%2010%20movers%20each%20day.%20I%20am%20looking%20for%20the%20value%20and%20code%20to%20populate%20automatically%20each%20day%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20formula%20i%20am%20trying%20in%20the%20spreadsheet%20attached%20(highlighted%20yellow).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20please%20help%20with%20this%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20a%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1064005%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EUser%20Adoption%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1064099%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20finding%20the%20biggest%20(absolute%20terms)%20movement%20in%20order%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1064099%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20a%20look%20at%20the%20attached%20workbook.%20Yours%20to%20begin%20with%20plus%20a%20few%20formulae%20in%20A10%3AB20.%20Took%20the%20liberty%20to%20adjust%20the%20pie-chart%20as%20well.%3C%2FP%3E%3CP%3EBy%20the%20way%2C%20this%20example%20takes%20only%20the%20top%2010%20positive%20contributors%20into%20account.%20Will%20get%20back%20soon%20with%20a%20solution%20that%20includes%20negatives%20as%20well.%3C%2FP%3E%3CP%3E%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1064244%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20finding%20the%20biggest%20(absolute%20terms)%20movement%20in%20order%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1064244%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20take%20sign%20into%20account%20that%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DAGGREGATE(14%2C6%2CABS(%24G%2448%3A%24G%242049)%2CROW()-ROW(%24B%249))*SIGN(INDEX(%24G%2448%3A%24G%242049%2CMATCH(AGGREGATE(14%2C6%2CABS(%24G%2448%3A%24G%242049)%2CROW()-ROW(%24B%249))%2CABS(%24G%2448%3A%24G%242049)%2C0)))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20for%20the%20name%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDEX(%24A%2448%3A%24A%242049%2CMATCH(%24B10%2C%24G%2448%3A%24G%242049%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1067260%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20finding%20the%20biggest%20(absolute%20terms)%20movement%20in%20order%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1067260%22%20slang%3D%22en-US%22%3E%3CP%3EHi%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%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20again%20for%20your%20assistance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExtremely%20helpful%20as%20always%2C%20much%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1067358%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20finding%20the%20biggest%20(absolute%20terms)%20movement%20in%20order%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1067358%22%20slang%3D%22en-US%22%3E%3CP%3EHi%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%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help%2Cit%20has%20worked%20perfectly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20did%20notice%20from%20the%20results%20that%20in%20this%20example%20i%20have%20many%20investments%20which%20are%20recorded%20a%20few%20times.%20Is%20there%20a%20way%20to%20tweek%20the%20formula%20to%20show%20the%20sum%20of%20these%20in%20the%20Contribution%20for%20each%20investment%2C%20and%20show%20in%20the%20order%20of%20movement%20as%20before%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20again%20for%20your%20assistance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1069235%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20finding%20the%20biggest%20(absolute%20terms)%20movement%20in%20order%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1069235%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20not%20my%20business%2C%20but%20are%20you%20sure%20that%20shall%20be%20the%20sum%3F%20I%20checked%20repeating%20names%2C%20for%20all%20of%20them%26nbsp%3B%3CSTRONG%3EContribtuion%20to%20Portfolio%20Movement%20(%25)%3C%2FSTRONG%3E%20is%20exactly%20the%20same.%20It%20looks%20like%20you%20already%20have%20cumulative%20figure%20here%20which%20are%20repeated%20for%20each%20name%20entry.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20that%20assumption%20is%20wrong%20I'd%20suggest%20to%20add%20helper%20column%20with%20SUMIF()%20of%20contribution%20%25%20for%20each%20name%20and%20use%20this%20column%20in%20initial%20formula%20instead%20of%26nbsp%3B%3CSTRONG%3EContribtuion%20to%20Portfolio%20Movement%20(%25).%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Frequent Contributor

Hi,

 

I have a large set of data i update each day. I am hoping to fix a formula so i can find the large movement (either positive or negative) each day. I am looking to find the top 10 movers each day. I am looking for the value and code to populate automatically each day

 

I have a formula i am trying in the spreadsheet attached (highlighted yellow).

 

Can someone please help with this formula.

 

Many thanks,

 

I a

4 Replies
Highlighted

@calof1 

If take sign into account that could be

=AGGREGATE(14,6,ABS($G$48:$G$2049),ROW()-ROW($B$9))*SIGN(INDEX($G$48:$G$2049,MATCH(AGGREGATE(14,6,ABS($G$48:$G$2049),ROW()-ROW($B$9)),ABS($G$48:$G$2049),0)))

and for the name

=INDEX($A$48:$A$2049,MATCH($B10,$G$48:$G$2049,0))
Highlighted
Solution

Hi@Sergei Baklan 

 

Thank you again for your assistance.

 

Extremely helpful as always, much appreciated.

 

Highlighted

Hi@Sergei Baklan 

 

Thanks for your help,it has worked perfectly.

 

I did notice from the results that in this example i have many investments which are recorded a few times. Is there a way to tweek the formula to show the sum of these in the Contribution for each investment, and show in the order of movement as before?

 

Thank you again for your assistance.

Highlighted

@calof1 

That's not my business, but are you sure that shall be the sum? I checked repeating names, for all of them Contribtuion to Portfolio Movement (%) is exactly the same. It looks like you already have cumulative figure here which are repeated for each name entry.

 

If that assumption is wrong I'd suggest to add helper column with SUMIF() of contribution % for each name and use this column in initial formula instead of Contribtuion to Portfolio Movement (%).