Raise matrix to power without VBA

You can do it with iterative calculation

  • check the box: file - options - formulas - "enable iterative calculations" (from the version Excel 2007)
  • set "maximum iterations" value to a number big enough (this is the maximum power you can raise the matrix)

enter image description here

  • Formula in helper cell:
    =IF(ISNUMBER(G2),IF(G2=E2,TEXT(G2,"0"),G2+1),IF(VALUE(G2)=E2,G2,1))
  • Formula in result matrix:
    =IF(ISNUMBER(G2),IF(G2=1,A2:C4,MMULT(A2:C4,H2:J4)),H2:J4)
    (this is an array formula, need to finish with CTRL+SHIFT+ENTER)

Notes

  • The formula is recalculated ONLY if you CHANGE POWER, it won't be recalculated after changing only the input matrix, neither at recalculation of all formulas (could be done but requires more complex formula and more helper cells, let me know if you're interested in it).
  • Also, after entering both formulas, output matrix will show only 0 values, to get it calculated you need to change the power, from that the sheet will be "initialized", it will work without problems
  • error checking is not implemented in the formula yet:
    • if power > maximum number of iterations, than formula will run only the times of iteration set
    • similarly, for non-integer or negative numbers formula won't stop, but run until max number of iterations
    • after maximum number of iterations reached formula will "overflow", counts forward without starting from 0, to fix that, set a power which will be reached in the next iteration cycle (e.g. if helper = 500, max number of iteration = 100, then set a 500 < power < 600, so formula will correctly stop there, and it'll work correctly for next setting of power).
  • majority of the issues described above can be fixed within the formula if necessary.

update

Answer to this question describe with more details how the formula works.


I don't think it can be done without a macro. If you come to the same conclusion, then here is a macro that'll do the job quickly on large powers: 

Function PowerMatrix(Matrix As Range, Power As Long) As Variant
    Dim Result As Variant
    Dim Square As Variant
    Dim i As Long

    Square = Matrix

    For i = 0 To 31
        If (Power And 2 ^ i) Then
            If IsEmpty(Result) Then
                Result = Square
            Else
                Result = Application.WorksheetFunction.MMult(Square, Result)
            End If
        End If
        If 2 ^ i >= Power Then Exit For
        Square = Application.WorksheetFunction.MMult(Square, Square)
    Next
    PowerMatrix = Result
End Function

If you cannot get around the restriction on VBA macros then maybe Excel isn't the correct tool to begin with. A webpage with javascript may be the better tool.

Tags:

Worksheet Function

Microsoft Excel

Recent Posts