putexcel: insert Excel equation from Stata

[Nick Bornschein]

Hi,

I would like to inset an Excel equation with the putexcel syntax from Stata. The equation ist: =If(E2<$I$1;"yes";"no")
I tried:
putexcel A1=("=If(E2<$I$1;"yes";"no")")
but Stata thinks that "yes" and "no" is some string informationen. How can I write the putexcel command to exactly put in the equation in Excel from Stata?

-Nick

[Nick Cox]

Never tried this, but I expect you need compound double quotes here.

help quotes

[Sergiy Radyakin]

Nick B., I believe when you write "equation" you mean "formula". There is no indication that putexcel supports writing formulas to Excel. If you follow NJC's advice, you end up with a string cell, containing the formula you showed, but not the result of evaluation of that formula as you probably expect.

Afaik putexcel writes values only, not formulas, but you can do the computations like you show in Stata, then write the result to Excel. If you want to create genuine formulas in Excel cells, either pre-define a template (if possible) or use other tools.

Best, Sergiy Radyakin.

[Nick Bornschein]

Thanks Sergiy.

[Andrew Dunn]

For anyone looking at this now, Nick's suggestion to use the quotes worked for me. Something like the following allowed me to directly input a formula into an Excel sheet. My Excel terminology is a little rusty, but the formula didn't actually calculate after being put there by Stata; the formula appeared as text in the sheet and didn't actually "calculate" until I double clicked the cell, at which point the formula evaluated. Still the best solution I've found so far

putexcel A1=`"=HYPERLINK("#Sheet1!A1","Sheet1")"'

[Sergiy Radyakin]

For anyone looking at this now, the discussion started a while ago, still around the times of Stata 13. By now, Stata 14 and Stata 15 support specifying formulas in Excel, see here:
https://www.stata.com/stata14/excel-cell-formatting/
and here:
https://blog.stata.com/2017/01/24/cr...a-expressions/

This works beautifully for the cases reviewed in the demonstration of Chuck Huber (StataCorp) but none of the examples involved treatment of the quoted content. More complex examples are welcomed. According to Stata's documentation:

Stata does not validate formulas; the text is passed literally to Excel.

But what we observe is that Stata strips the quotes from the formula.

version 15.0

putexcel set "C:\temp\junk.xlsx", replace

putexcel A1 = formula(B1+C1)
putexcel B1 = 7
putexcel C1 = 3
// works fine until here

putexcel A100 = formula(HYPERLINK("#Sheet1!A1", "Jump to A1") )
// this already creates an invalid file

Here is an in depth look:

The top panel shows what is expected - quoted content. The bottom portion is what Stata actually generates in output, note the quotes are removed from the formula. If the quotes can be preserved, that would result in a valid link.

Best, Sergiy Radyakin

[Ale Aguilar]

Does anyone has any idea of how to solve this problem? I'm trying to use de excel formula =if(G6<=0.05,"*", " ") but it simply won't work (produces an invalid file).
I would greatly appreciate help with this issue.

Thank you all
Best,
Alejandra Aguilar