Using Python (Excel Beta) with Lambda functions

Question

Hey everyone :)

I've been using Python in the Beta channel of Excel, and it's been a great experience for me so far!

I noticed recently that when I am referencing a cell containing a lambda function for a Python calculation, I'm getting an error.

For example,

Cell B17 = 5.0 (Lambda function output)

Cell B18 = 3.2 (User entry)

Cell B19 = 0.1 (User entry)

Cell B21 = #CALC! (Python calculation that references B17, B18, B19)

I'm not getting errors if the Python calculation only references cells B18 & B19.

I hope that makes sense. I would love to get some help on what I could try to get this working.

If I can provide any further explanations, please let me know!

Answer 1

Hi adi_h_,

I am pleased to hear about your response. I'm glad that it's working with basic lambda functions. As we all know that Python in Excel is still in its infancy and further development is ongoing, I strongly believe this could be a feature limitation with Python in Excel when using more advanced functions. It appears that it's still not capable of analyzing complex calculations.

The best option we can do for now, adi_h_, is to provide feedback to our developers so they can be aware of this limitation and issues with Python. In that way they can look into it and find ways to include in the next updates. Kindly click the Help > Feedback in Excel to provide your feedback. As for your question, yes, you are free to repost this to the community we suggested.

I hope this helps! It's a true pleasure to assist you, adi_h_. Thanks for your contribution on the development of Python in Excel!

Sincerely,
Froilance T.

Microsoft Moderator

Answer 2

Thank you so much for your kind support, Froilance

I am currently testing some of the workarounds you suggested (which is a fantastic start!), and I'm crossing my fingers this works out in the future :)

Thank you again!

Adi

Answer 3

Thank you so much for your kind response, Froilance :)

Just to give you a brief update, I tried some simpler lambda functions (e.g., LAMBDA(x, y, x+y) ) and that worked perfectly well with Python, so that was a nice surprise :)

I had an INDEX-MATCH statement in the original lambda function ('Measure' in the screenshot), so I tried removing the lambda function and coded the INDEX-MATCH directly into the cell.

Interestingly, that started throwing a #CALC error from Python. Perhaps this is an INDEX issue with Python, not LAMBDA? I'm not so sure. It seems to work with some INDEX functions but not with others...

As for your great suggestion - you are spot on; that works :) In this case, I'm looking for a dynamic solution, i.e., I would love the Python code to give me an updated result when one of the referenced cells changes because of a formula. Do you perhaps have a suggestion that could help this case?

Thank you again for your time! Please let me know if I can repost this in your suggested communities.

Best,

Adi

Answer 4

Hello adi_h,

Thanks for writing us here in Microsoft Community.

We're glad to hear that you're enjoying using Python in the Excel Beta channel. Regarding your issue with referencing a cell containing a lambda function for a Python calculation, it's possible that the lambda function is not being recognized as a valid input for the Python calculation.

Based on my research, it appears that this is a known issue with the LAMBDA function in Excel. I'll try my best to help you, adi_h, but if it doesn't apply to your issue, it's best if we could seek help from dedicated experts for further guidance.

One option you could try is to convert the lambda function output in cell B17 to a regular value using the "Copy" and "Paste Values" functions. This will replace the lambda function output with a regular value that can be recognized by the Python calculation.

To do this, select cell B17, right-click and select "Copy", then right-click on another cell and select "Paste Values". This will replace the lambda function output with a regular value that can be used in your Python calculation.

Other workaround that you can try is to use a named range instead of a lambda function. Here are the steps:

1. Select the cell containing the lambda function.
2. Go to Formulas > Define Name.
3. In the New Name dialog box, enter a name for the range and select the cell containing the lambda function as the Refers to value.
4. Click OK.
5. In your Python calculation, reference the named range instead of the cell containing the lambda function.

To learn more about the error encountered, you can also try to check these articles.

• Troubleshoot Python in Excel errors - Microsoft Support
• How to correct a #CALC! error - Microsoft Support

If this workaround does not resolve the issue, we strongly recommend you post this to Questions - Microsoft Q&A or Welcome to the Excel Community - Microsoft Community Hub. Experts from these forums are dedicated to help advanced users like you. They will be able to guide you and provide further assistance in addressing the issue.

If you continue to encounter issues, please report them by selecting Help > Feedback in Excel. We will also leave this thread open so that other MVPs and community members can participate.

These are my ways of helping you today. I hope you understand.

Sincerely,
Froilance T.

Microsoft Moderator