title: Display formula in cell (average function)
body: <p>I have some data:</p>
  <p><a href="https://i.stack.imgur.com/ZBx4P.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ZBx4P.png" alt="enter image description here" /></a></p>
  <p>For each column, I want to calculate the average of the most current year. In the example below, I want to calculate it for 2020, so rows 164, 165 and 166. The result should appear in row 163.</p>
  <p>However, I want not just the result, I want the formula so the user can see what is being calculated, but I can't. Maybe relevant: I'm using German Excel. I tried using <code>.FormulaLocal</code> and <code>MITTELWERT</code>(German for <code>AVERAGE</code>) but I think I'm making a different kind of mistake. With both <code>.Formula</code> and <code>.FormulaLocal</code> I'm getting a run time error 13 (type) in the line with the <code>.Formula</code>.</p>
  <pre><code>Sub FormulaLeased(ByVal fRow As Long, ByVal lCol As Long, ws As Worksheet)
      Dim i       As Long
      Dim iCol    As Long
      
     'manually setting values for this example:
      fRow = 164
      lCol = 7
      Set ws = ActiveSheet
  
      With ws
          Select Case Mid(.Cells(fRow, &quot;A&quot;).Value, 4, 2) 'Determining the most recent quarter 
          Case &quot;12&quot;
              i = 3
          Case &quot;09&quot;
              i = 2
          Case &quot;06&quot;
              i = 1
          Case &quot;03&quot;
              i = 0
          End Select
          
          For iCol = 2 To lCol
              '---works, but only shows the result, not the forumla:                
              '.Cells(fRow - 1, iCol).Value = Application.WorksheetFunction.Average(.Range(.Cells(fRow, iCol), .Cells(fRow + i, iCol))) 
  
              '---results in type error:
              .Cells(fRow - 1, iCol).Formula = &quot;= Application.WorksheetFunction.Average(&quot; &amp; .Range(.Cells(fRow, iCol).Address(False, False) &amp; &quot;:&quot; &amp; .Cells(fRow + i, iCol).Address(False, False)) &amp; &quot;)&quot; 
  
          '---trying to use .FormulaLocal -&gt; Type error
          '.Cells(fRow - 1, iCol).FormulaLocal = &quot;= MITTELWERT(&quot; &amp; .Range(.Cells(fRow, iCol).Address(False, False) &amp; &quot;:&quot; &amp; .Cells(fRow + i, iCol).Address(False, False)) &amp; &quot;)&quot;
          Next iCol
      End With
  End Sub
  </code></pre>