title: Formulas with Excel Interop
body: <p>I'm using F# and Excel Interop to output data to an Excel spreadsheet. My first approach was to individually set each cell:</p>
  
  <pre><code>worksheet.Range(range1).Value2 &lt;- "=sum(a1:a10)"
  worksheet.Range(range2).Value2 &lt;- "=min(a1:a10)"
  worksheet.Range(range3).Value2 &lt;- "=max(a1:a10)"
  (* etc *)
  </code></pre>
  
  <p>However, this is too slow when there is a large number of formulas, so I switched to an array:</p>
  
  <pre><code>worksheet.Range(range).Value2 &lt;- [| "=sum(a1:a10)"
                                      "=min(a1:a10)"
                                      "=max(a1:a10)" |]
  </code></pre>
  
  <p>or</p>
  
  <pre><code>worksheet.Range(range).Formula &lt;- [| "=sum(a1:a10)"
                                       "=min(a1:a10)"
                                       "=max(a1:a10)" |]
  </code></pre>
  
  <p>However, now Excel just displays those strings in the cells, instead of calculating the value of the formula. I also tried:</p>
  
  <pre><code>worksheet.Range(range).FormulaArray &lt;- [| "=sum(a1:a10)"
                                            "=min(a1:a10)"
                                            "=max(a1:a10)" |]
  </code></pre>
  
  <p>But that messed with the target ranges of each formula. I'm not sure what's going on there. (Instead of leaving it as <code>"a1:a10"</code>, Excel translated the target ranges based on where the formula would be.)</p>
  
  <p>Is there a better way to do this?</p>
  
  <p><strong>Update</strong>: I also tried using "=MIN($G$2:$G$5)", but that produced the same effect, where Excel is just displaying the string and not evaluating the formula. </p>
  
  <p>For what it's worth, when if I enter the cell, erase and then re-add any character in the formula, when I hit "enter", Excel will evaluate it.</p>
  
  <p><strong>Update 2</strong>: I also tried the following:</p>
  
  <pre><code>worksheet.Range(range).FormulaR1C1 &lt;- [| "=sum(R2C[0]:R4C[0])"
                                            "=average(R2C[0]:R4C[0])"
                                            "=min(R2C[0]:R4C[0])"
                                            "=max(R2C[0]:R4C[0])" |]
  </code></pre>
  
  <p>But I get the same problem: Excel renders them as strings instead of evaluating the formula.</p>