Formula getting updated in every cells automatically

%3CLINGO-SUB%20id%3D%22lingo-sub-2317720%22%20slang%3D%22en-US%22%3EFormula%20getting%20updated%20in%20every%20cells%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2317720%22%20slang%3D%22en-US%22%3E%3CP%3EMatz%2C%20kindly%20describe%20your%20issue%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20initiate%20a%20formula%20taken%20from%20Real%20Statistics%2C%20where%20an%20output%20is%20given%20as%20a%20matrix.%20However%2C%20when%20I%20initiate%20the%20formula%2C%20I%20receive%20an%20error%20in%20one%20cell%20(%23VALUE!)%20and%20the%20matrix%20fails%20to%20form.%20I%20think%20I%20may%20have%20an%20issue%20with%20the%20spill%20ability%20in%20excel%20to%20populate%20an%20array%20with%20data%20from%20my%20formula.%20Any%20help%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFormula%20used%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3E%3D(MMULT(TRANSPOSE(B3%3ABH135-B137%3ABH137)%2CB3%3ABH135-B137%3ABH137)%2F(COUNT(B3%3AB135)-1))%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2317720%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-2317947%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20getting%20updated%20in%20every%20cells%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2317947%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1043262%22%20target%3D%22_blank%22%3E%40matzindergard%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESame%20question%20in%20the%20link%20below.%20Already%20answered.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fenable-autospill%2Fm-p%2F2317760%23M98534%22%20target%3D%22_self%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fenable-autospill%2Fm-p%2F2317760%23M98534%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2318529%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20getting%20updated%20in%20every%20cells%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2318529%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1043262%22%20target%3D%22_blank%22%3E%40matzindergard%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20you%20have%20text%20(e.g.%20empty%20string)%20within%26nbsp%3B%3CEM%3EB3%3ABH135%20and%2For%20B137%3ABH137%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Matz, kindly describe your issue:

 

I am trying to initiate a formula taken from Real Statistics, where an output is given as a matrix. However, when I initiate the formula, I receive an error in one cell (#VALUE!) and the matrix fails to form. I think I may have an issue with the spill ability in excel to populate an array with data from my formula. Any help would be greatly appreciated.

 

Formula used: 

=(MMULT(TRANSPOSE(B3:BH135-B137:BH137),B3:BH135-B137:BH137)/(COUNT(B3:B135)-1))

2 Replies

@matzindergard 

As variant you have text (e.g. empty string) within B3:BH135 and/or B137:BH137