Writing multiple Excel Values with PY #32

Activity

1. keyur32 commented on Sep 20, 2023

   

   Keyur Patel (keyur32)

   on Sep 20, 2023

   Collaborator

   More actions

   Python formulas only return data within the cell they were entered on. https://support.microsoft.com/en-us/office/data-security-and-python-in-excel-33cc88a4-4a87-485e-9ff9-f35958278327.

   To write to other cells, you would need to manually enter or do it via automation (i.e. an Office Script to write out the =PY formula) to the desired cell.
2. jflam commented on Sep 25, 2023

   

   John Lam (jflam)

   on Sep 25, 2023

   More actions

   You can also spill additional values back into the grid as well if you return lists, tuples or Pandas DataFrames from the Python formula.
3. WillemWannenburg commented on Oct 23, 2023

   

   Willem (WillemWannenburg)

   on Oct 23, 2023

   More actions

   ahagmann83, have you found a work-around way to write from Python to Excel?
4. jflam commented on Oct 23, 2023

   

   John Lam (jflam)

   on Oct 23, 2023

   More actions

   ahagmann83, have you found a work-around way to write from Python to Excel?

   There are no workarounds for this, as this behavior is by design. What is your scenario?
5. WillemWannenburg commented on Oct 24, 2023

   

   Willem (WillemWannenburg)

   on Oct 24, 2023

   More actions

   I regularly build physics models on excel that calculate a certain output value based on given parameters. Then I always want to plot the relationship that a chosen input parameter has to the output. If I worked only in Python, it's easy: I would make a function and iterate (loop) to plot. Now, I want to achieve the same but using python in excel.
   See the example below. Here I want to plot input parameter (C2) against output (C35). I achieved the desired output in "sheet=DP-Python", but this is not elegant enough, I would then rather just do it all in python.
   I tried using the fact that python in excel goes left to right and then down, but then I get a circular ref issue.

   Pressure analysis - python for excel.xlsx

   If there is an elegant way to do this, it would work well for monte carlo simulations as well.
   I know I can do this using a macro, but again then I'll just rather do it all in Python.
6. salabim commented on Oct 24, 2023

   

   salabim

   on Oct 24, 2023

   More actions

   I would think that you can dump several output variables as a dataframe on your sheet. From there you could possibly reference to other cells on your sheet(s).
   If you want, we could discuss the details in private.
7. WillemWannenburg commented on Oct 25, 2023

   

   Willem (WillemWannenburg)

   on Oct 25, 2023

   More actions

   In summary what I would want to do in a python cell in excel:

   for i in [0,5,10,15,20,25,30,35,40,45,50]:
       xl("C2") = i  #write to excel
       output_list.append(xl("C35")) 
   output_list
   
8. WillemWannenburg commented on Oct 30, 2023

   

   Willem (WillemWannenburg)

   on Oct 30, 2023

   More actions

   I found out I can achieve my goal using a What-if -> Data Table. https://www.reddit.com/r/excel/comments/5sdlng/whatif_analysis_question/