#VALUE! error for cells with a dict over a certain size

Python in Excel Anaconda Code
1

I’m running into an error with =ANACONDA.CODE(“py”,…) that I’m not running into with =PY().

For example, this correctly return a python object “</> dict” in excel:

=ANACONDA.CODE("py", "### Do Not Edit this formula directly. Use Anaconda Code to modify.
retdata = {'a': [], 'b': []}

for _ in range(5):
    retdata['a'].append(list(range(0,350)))
    retdata['b'].append(list(range(0,350)))
    
retdata

", 3)

but changing that to 50 elements, for example:

...
for _ in range(50):
...

results in a #VALUE! error and I can no longer reference the data in that cell. is there any work-arounds for this?

2

Hi and welcome!

This is an interesting case. For reference I’ve found that any combination of x and y that results in more than 7000 elements in the flattened lists gives the same problem.


retdata = {'a': [], 'b': []}

x = 21
y = 334

for _ in range(x):
    retdata['a'].append(list(range(0,y)))
    retdata['b'].append(list(range(0,y)))

print((x)*y)
retdata

We will definitely look into why that’s happening when displayed in Excel. The good news is that it doesn’t affect the object itself. You can still reference it without problem in the cell in which the dictionary is created. Or you can reference it outside of that cell using Linked Mode. On the original cell, ensure Linked Mode is selected (the toggle is under the script). When Linked Mode is active, it will say “Run Linked”.

image
image389×362 9.24 KB

Then you can create another cell, also in Linked Mode, and refer to the dictionary directly instead of using REF.

image
image389×514 16.4 KB

You can read more about Linked and Isolated mode here.

We will look into the display issue for large dictionaries. Thanks for the feedback!

3

Thanks for the quick response Owen,

happy to see that I’m not crazy and the issue is reproducible.

My original code was based on the =PY() functions “built into” Excel, but I found that even with a paid Python in Excel license, I was still having my monthly access cut off (apparently the paid license only increases the amount of “premium” cloud time, but total time is not unlimited). I tried modularizing my code so that each cell had a subset of the data I was calculating, and so I would reference that cell to use its data; though Excel was still re-calculating everything every time something changed and it was just chewing through my cloud allotment.

I’m in the process of doing exactly what you suggested, however, and reverting my code back to using global variables that I reference directly since there shouldn’t be those same caps when executing locally.

4

Ok sounds good. Let me know if you run into any difficulties.

Regarding premium compute on Python in Excel - please make sure that the files you’re opening are stored under the same account as the premium license is applied to. Some months ago I had an issue where I had a file stored in a OneDrive account that didn’t have a premium compute license, and I was opening it from another M365 account that did have a premium license, thinking that I could just use the premium compute on that file.

I found that if the file is not stored in the same account, the premium compute is not used. Since I’ve started keeping all my Python in Excel files on the account that has the premium compute license, I’ve never run into any messages about running out of compute.

Hopefully that helps.

5

Thanks for finding this issue!

The fix should be pretty simple. We need to trim the preview field in the card view if it’s over a certain size to avoid limitations imposed by Excel.

I’ll let you know when the fix is deployed. In the meantime, use the workaround suggested by Owen.