Looking for Cumulative Sum Formula that returns Dynamic Arrays (i.e. spill-able)

%3CLINGO-SUB%20id%3D%22lingo-sub-2309337%22%20slang%3D%22en-US%22%3ELooking%20for%20Cumulative%20Sum%20Formula%20that%20returns%20Dynamic%20Arrays%20(i.e.%20spill-able)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2309337%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Excel%20Community%2C%3C%2FP%3E%3CP%3EI%20have%20a%20simple%20row%20of%20numbers%20with%20a%20range%20name%20called%20Numbers%3C%2FP%3E%3CP%3EI'm%20looking%20for%20a%20spill%20formula%20(one%20that%20returns%20a%20dynamic%20array)%20that%20returns%20a%20cumulative%20sum.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20understand%20there%20are%20options%20for%20the%20non-spillable%20ways%20to%20generate%20the%20**bleep**%20such%20as%20%3Dsum(%24a%241%3Aa2)%20and%20drag%20this%20formula%20across%20the%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2309337%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2309601%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20Cumulative%20Sum%20Formula%20that%20returns%20Dynamic%20Arrays%20(i.e.%20spill-able)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2309601%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1037339%22%20target%3D%22_blank%22%3E%40PapaAustin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EYou%20may%20try%20this%20%3A%3C%2FEM%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DSUM(INDEX(NmaedRange%2C1%2C1)%3AINDEX(NamedRange%2CROW()-ROW(INDEX(NamedRange%2C1%2C1))%2B1))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EN.B.%20%3C%2FSTRONG%3E%3CEM%3EReplace%20NamedRange%20with%20the%20NAME%20you%20have%20given%20to%20the%20RANGE.%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2310601%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20Cumulative%20Sum%20Formula%20that%20returns%20Dynamic%20Arrays%20(i.e.%20spill-able)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2310601%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1037339%22%20target%3D%22_blank%22%3E%40PapaAustin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPossible%20solutions%20are%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fstackoverflow.com%2Fquestions%2F59474792%2Fcumulative-sum-formula-using-new-excel-dynamic-array-formulas%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3ECumulative%20Sum%20Formula%20using%20new%20Excel%20Dynamic%20Array%20Formulas%20-%20Stack%20Overflow%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EExample%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20506px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F277046i681081599D752C01%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2310626%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20Cumulative%20Sum%20Formula%20that%20returns%20Dynamic%20Arrays%20(i.e.%20spill-able)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2310626%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1037339%22%20target%3D%22_blank%22%3E%40PapaAustin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPicture%20disappeared.%20Repeat%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20412px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F277049i4CBD90CAAD9C0FCA%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2311357%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20Cumulative%20Sum%20Formula%20that%20returns%20Dynamic%20Arrays%20(i.e.%20spill-able)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2311357%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F93699%22%20target%3D%22_blank%22%3E%40Rajesh-S%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EThanks%20for%20looking%20at%20my%20post.%20But%20unfortunately%2C%20this%20formula%20does%20not%20spill.%3C%2FP%3E%3CP%3EMaking%20it%20spillable%20is%20what%20makes%20this%20particular%20formula%20exercise%20tricky%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20not%20sure%20if%20there%20is%20a%20good%20way%20to%20do%20this%20without%20using%20Lambda%20(which%20I%20currently%20do%20not%20have%20an%20access%20for)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2311642%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20Cumulative%20Sum%20Formula%20that%20returns%20Dynamic%20Arrays%20(i.e.%20spill-able)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2311642%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1037339%22%20target%3D%22_blank%22%3E%40PapaAustin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20is%20spill%2C%20please%20check%20attached.%20If%20you%20change%20spill%20in%20B2%20formula%20follows%20it.%3C%2FP%3E%0A%3CP%3EIf%20you%20may%20do%20something%20without%20lamdas%2C%20do%20it.%20Lambdas%20in%20Excel%20are%20at%20very%20beginning%2C%20if%20you%20do%20something%20using%20them%20now%2C%20in%20few%20months%20most%20probably%20it%20could%20be%20done%20much%20easier.%20Also%20with%20lambdas.%20IMHO%2C%20using%20lambdas%20now%20is%20only%20to%20train%20the%20brains%20and%20be%20prepared%20to%20future%20Excel.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2311673%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20Cumulative%20Sum%20Formula%20that%20returns%20Dynamic%20Arrays%20(i.e.%20spill-able)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2311673%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%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%20Sergei%2C%3C%2FP%3E%3CP%3EI%20was%20replying%20to%20another%20commenter%20(his%20solution%20was%20using%20Sum(index...%20%3Aindex)%20approach%20which%20does%20not%20spill.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYours%20look%20like%20it%20would%20spill%20as%20you%20pointed%20out.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20do%20not%20have%20an%20access%20to%20the%20latest%20O365%20update%20with%20Let%20and%20Lambda%20features%20(I'm%20on%20an%20enterprise%20license%20and%20probably%20won't%20have%20those%20features%20until%20much%20later)%3B%20I%20tried%20your%20file%20but%20it%20gives%20me%20%23name%20error%20because%20my%20Excel%20doesn't%20know%20what%20Let()%20is.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20far%2C%20i%20have%20found%20this%20approach%3B%20it%20is%20rather%20a%20lengthy%20formula%20but%20it%20works.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DTRANSPOSE(MMULT(%3CBR%20%2F%3E(COLUMN(%3CEM%3EMyRange%3C%2FEM%3E)%20%26lt%3B%3D%20TRANSPOSE(COLUMN(%3CEM%3EMyRange%3C%2FEM%3E)))*%3CEM%3EMyRange%3C%2FEM%3E%2C%3CBR%20%2F%3ETRANSPOSE(SIGN(%3CEM%3EMyRange%3C%2FEM%3E))%3CBR%20%2F%3E))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2311710%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20Cumulative%20Sum%20Formula%20that%20returns%20Dynamic%20Arrays%20(i.e.%20spill-able)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2311710%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1037339%22%20target%3D%22_blank%22%3E%40PapaAustin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELET()%20shall%20be%20in%20production%2C%20perhaps%20except%20deferred%20semi-annual%20channel%2C%20not%20sure.%20MMULT()%20works%20fine%2C%20but%20that's%20not%20exactly%20the%20spill.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2311770%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20Cumulative%20Sum%20Formula%20that%20returns%20Dynamic%20Arrays%20(i.e.%20spill-able)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2311770%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1037339%22%20target%3D%22_blank%22%3E%40PapaAustin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20rewrite%26nbsp%3B%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%3Bformula.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUBTOTAL(9%2COFFSET(B2%23%2C0%2C0%2CSEQUENCE(ROWS(B2%23))))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2313073%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20Cumulative%20Sum%20Formula%20that%20returns%20Dynamic%20Arrays%20(i.e.%20spill-able)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2313073%22%20slang%3D%22en-US%22%3EPlease%20share%20some%20sample%20data%20along%20with%20the%20expected%20output%2C%2C%2C%20otherwise%20what%20I've%20suggested%20%26amp%3B%20by%20%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%20are%20working%20for%20Cumulative%20Sum%20as%20you%20have%20mentioned%20with%20the%20post%20!!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2313155%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20Cumulative%20Sum%20Formula%20that%20returns%20Dynamic%20Arrays%20(i.e.%20spill-able)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2313155%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1037339%22%20target%3D%22_blank%22%3E%40PapaAustin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAt%20present%2C%20I%20do%20not%20believe%20there%20is%20any%20completely%20satisfactory%20way%20of%20performing%20a%20simple%20balance%20calculation%20from%20a%20flow%20array%20using%20dynamic%20array%20formulas.%26nbsp%3B%20Currently%20this%20represents%20the%20most%20significant%20roadblock%20that%20prevents%20Excel%20models%20becoming%20dynamic%20in%20their%20entirety.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EComputationally%2C%20the%20most%20efficient%20solution%20is%20to%20buy%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F137306%22%20target%3D%22_blank%22%3E%40Charles%20Williams%3C%2FA%3E's%20Fast%20Excel%20add-in%20that%20has%20a%20function%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20ACCUMULATE(array%2C%20startValue%2C%20factors%2C%20open)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Ewhere%20factors%20allow%20for%20interest%20based%20on%20a%20prior%20balance%20or%20growth%20calculations.%3C%2FP%3E%3CP%3E%26nbsp%3B%3CA%20href%3D%22http%3A%2F%2Fwww.decisionmodels.com%2FFastExcelV4.htm%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EWhy%20FastExcel%20V4%20-%20Decision%20Models%3C%2FA%3E%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20obvious%20calculation%20of%20adding%20the%20array%20to%20an%20offset%20copy%20of%20itself%2C%20works%20for%20CSE%20array%20formulas%20but%20not%20DA%20because%20DA%20does%20not%20support%20array%20breakup%20%5BCSE%20arrays%20flow%20just%20about%20as%20well%20as%20set%20concrete%5D.%3C%2FP%3E%3CP%3EOther%20methods%2C%20such%20as%20using%20%3CSTRONG%3EMMULT%3C%2FSTRONG%3E%20with%20an%20upper%20triangular%20matrix%20of%201s%2C%20or%20%3CSTRONG%3ESUMIFS%3C%2FSTRONG%3E%20in%20which%20flow%20variables%20are%20added%20from%20earlier%20periods%20are%26nbsp%3B%3CSTRONG%3EO(n%C2%B2)%3C%2FSTRONG%3E%20and%20eventually%20become%20computationally%20unaffordable.%26nbsp%3B%20The%20latter%20are%2C%20however%2C%20effective%20for%20small%20to%20medium-sized%20arrays%20%5B20%2C000%20terms%20would%20start%20to%20be%20a%20problem%5D.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEven%20with%20Lambda%20functions%20one%20does%20not%20entirely%20escape%20problems.%26nbsp%3B%20At%20the%20simplest%20level%2C%20it%20can%20be%20used%20as%20a%20'wrapper'%20to%20make%20%3CSTRONG%3EMMULT%3C%2FSTRONG%3E%20more%20acceptable%20to%20a%20non-mathematical%20user%20but%20that%20does%20not%20change%20the%20efficiency%20issue.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERecursion%20overcomes%20the%20problem%20of%20an%20array%20formula%20not%20being%20able%20to%20reference%20earlier%20terms%20of%20itself%20without%20generating%20a%20circular%20reference%20error.%26nbsp%3B%20Since%20every%20level%20of%20a%20recursion%20stack%20contains%20an%20image%20of%20every%20variable%2C%20care%20needs%20to%20be%20taken%20not%20to%20pass%20large%20arrays.%26nbsp%3B%20There%20are%20currently%20quite%20severe%20limitations%20on%20the%20depth%20allowed%20for%20recursion%20which%20require%20careful%20management%20%5Btechniques%20involving%20passing%20data%20as%20functions%20to%20be%20evaluated%20later%2C%20rather%20than%20as%20arrays%2C%20or%20traversing%20the%20problem%20within%20a%20bisection%20tree%20rather%20than%20a%20list%20-%20not%20for%20the%20faint-hearted!%5D.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello Excel Community,

I have a simple row of numbers with a range name called Numbers

I'm looking for a spill formula (one that returns a dynamic array) that returns a cumulative sum.

 

I understand there are options for the non-spillable ways to generate the **bleep** such as =sum($a$1:a2) and drag this formula across the row.

 

Thank you! 

 

 

12 Replies

@PapaAustin 

 

You may try this :

 

 

=SUM(INDEX(Mydata,1,1):INDEX(Mydata,ROW()-ROW(INDEX(Mydata,1,1))+1))

 

 

 

Rajesh-S_0-1619768900214.png

  • Formula in cell S86, fill it down:

 

 

N.B. Replace Mydata (is Named Range) with the NAME you have given to the RANGE.

@Rajesh-S 

Hello,

Thanks for looking at my post. But unfortunately, this formula does not spill.

Making it spillable is what makes this particular formula exercise tricky

 

I'm not sure if there is a good way to do this without using Lambda (which I currently do not have an access for)

@PapaAustin 

It is spill, please check attached. If you change spill in B2 formula follows it.

If you may do something without lamdas, do it. Lambdas in Excel are at very beginning, if you do something using them now, in few months most probably it could be done much easier. Also with lambdas. IMHO, using lambdas now is only to train the brains and be prepared to future Excel.

 

@Sergei Baklan 

Hello Sergei,

I was replying to another commenter (his solution was using Sum(index... :index) approach which does not spill.

 

Yours look like it would spill as you pointed out. 

I do not have an access to the latest O365 update with Let and Lambda features (I'm on an enterprise license and probably won't have those features until much later); I tried your file but it gives me #name error because my Excel doesn't know what Let() is.

 

So far, i have found this approach; it is rather a lengthy formula but it works.

 

=TRANSPOSE(MMULT(
(COLUMN(MyRange) <= TRANSPOSE(COLUMN(MyRange)))*MyRange,
TRANSPOSE(SIGN(MyRange))
))

@PapaAustin 

LET() shall be in production, perhaps except deferred semi-annual channel, not sure. MMULT() works fine, but that's not exactly the spill.

@PapaAustin 

You could rewrite @Sergei Baklan formula.

=SUBTOTAL(9,OFFSET(B2#,0,0,SEQUENCE(ROWS(B2#))))
Please share some sample data along with the expected output,,, otherwise what I've suggested & by @Sergei Baklan are working for Cumulative Sum as you have mentioned with the post !!

@PapaAustin 

At present, I do not believe there is any completely satisfactory way of performing a simple balance calculation from a flow array using dynamic array formulas.  Currently this represents the most significant roadblock that prevents Excel models becoming dynamic in their entirety.

 

Computationally, the most efficient solution is to buy @Charles Williams's Fast Excel add-in that has a function

= ACCUMULATE(array, startValue, factors, open)

where factors allow for interest based on a prior balance or growth calculations.

 Why FastExcel V4 - Decision Models  

 

The obvious calculation of adding the array to an offset copy of itself, works for CSE array formulas but not DA because DA does not support array breakup [CSE arrays flow just about as well as set concrete].

Other methods, such as using MMULT with an upper triangular matrix of 1s, or SUMIFS in which flow variables are added from earlier periods are O(n²) and eventually become computationally unaffordable.  The latter are, however, effective for small to medium-sized arrays [20,000 terms would start to be a problem].

 

Even with Lambda functions one does not entirely escape problems.  At the simplest level, it can be used as a 'wrapper' to make MMULT more acceptable to a non-mathematical user but that does not change the efficiency issue. 

 

Recursion overcomes the problem of an array formula not being able to reference earlier terms of itself without generating a circular reference error.  Since every level of a recursion stack contains an image of every variable, care needs to be taken not to pass large arrays.  There are currently quite severe limitations on the depth allowed for recursion which require careful management [techniques involving passing data as functions to be evaluated later, rather than as arrays, or traversing the problem within a bisection tree rather than a list - not for the faint-hearted!].

@Rajesh-S

 

Just read your proposed solution to this topic and it works perfectly for me in a worksheet (i.e. using columns & rows). The issue however is that I would like to do similar in a set of named arrays that do not have any associated rows, columns or cells.

I'm trying to implement the Adler-32 Checksum Algorithm (https://en.wikipedia.org/wiki/Adler-32) in Excel but without using any VBA. That works perfectly using cell ranges but not using a set of named arrays.

 BCDE
2 ASCIIA32-AA32-B
3String:878888
4Wikipedia105193281
5 107300581
6Adler-32 Hex:105405986
711E603981125171503
8 1016182121
9Adler-32 Base64:1007182839
10EeYDmA1058233662
11 979204582

 

Formula =CODE(MID(B4,ROW(INDIRECT("1:"&LEN(B4))),1)) in C2 converts the string "Wikipedia" into and array of ASCII characters (range C3:C11 is named ASCII) so array formula {=SUM(INDEX(ASCII,1):INDEX(ASCII,ROW()-ROW(INDEX(ASCII,1))+1))+1} in D2 computes the A part of Adles-32 and {=SUM(INDEX(Adler32A,1):INDEX(Adler32A,ROW()-ROW(INDEX(Adler32A,1))+1))+0} takes care of the B Part.

Formula =DEC2HEX(BITLSHIFT(MAX(E:E),16)+MAX(D:D),8) in B7 the calculates the final Adler-32 checksum.

I would like the arrays ASCII, Alder32A and Adlre32B to only exist as named (Name Manager) arrays but cannot figure out how to then change the array formulas that are now in cols D & E

 

Can you perhaps shed some light on that?

 

Thx

@Voorbijstreven 

Maybe the title "Looking for Cumulative Sum Formula" should now be "Waiting for Cumulative Sum Formula".  A certain amount was possible before

Ways of performing Accumulation with Dynamic Arrays - Page 2 - Microsoft Tech Community

but it seems that a definitive set of functions are emerging

Announcing LAMBDA Helper Functions (microsoft.com)