User Defined Array formulas in DataNitro

Question

I have created a python function to exponentiate a matrix and imported it as a user defined function in Excel using DataNitro. However, when I use this formula I get the result as a python list in one cell.

Is it possible to create array formulas for excel using DataNitro similar in lines to MMULT or MINVERSE?

Below is my python code and a snapshot of the excel sheet:

functions.py

import numpy as np

def mat_expo(x,n):
    if(n==2):
        return mat_mult(x,x)
    else:
        return mat_mult(x,mat_expo(x,n-1))

def mat_mult(x,y):
    return np.dot(x,y)

Excel Sheet:

my excel sheet

score 0 · Accepted Answer · answered May 29 '16 at 19:00

There's no way to return to multiple cells from one DataNitro function. You can get the same result as follows:

def mat_expo_entry(x, n, i, j):
    return mat_expo(x, n)[i][j]

Use =mat_expo_entry($B$2:$D$4,3,ROW()-7,COLUMN()-2) in your sample spreadsheet in cells B7:D9 to print the output correctly.

If this is slow, you can store the answer using Python's built-in lru cache:

from functools32 import lru_cache # use 'functools' in Python3

@lru_cache
def mat_expo(x,n):
if(n==2):
    return mat_mult(x,x)
else:
    return mat_mult(x,mat_expo(x,n-1))

Alternatively, you could use a DataNitro script to print the result directly to the sheet.

User Defined Array formulas in DataNitro

1 Answers1