Excel re-use range that is used by another function

Question

I have a cell C1, where C1 =AVERAGE(E1:E10). In cell D1 I would like to have D1 =STDEVP(E1:E10) without explicitly typing the range E1:E10 as I need to use this and other ranges multiple times.

Is there a simple way to get/refer to the affected range in C1 for use by another function in another cell? Something like D1 =STDEVP(AFFECTEDRANGE(C1)).

I found a function called INDIRECT which roughly does what I want, but it requires additional columns for my purpose. As I prefer to keep my worksheet clean and compact I'd prefer a function as described above. Does a one-liner like this exist?

The best method is INDIRECT. You can also try parsing the FORMULATEXT() to get the range but you will still need to wrap that in INDIRECT. — Scott Craner, Sep 06 '16 at 13:20

score 1 · Answer 1 · answered Sep 06 '16 at 13:21

1

A Replace on the Range.Formula property would seem to be sufficient.

range("d1").formula = replace(range("c1").formula, "AVERAGE", "STDEVP", vbtextcompare)

If you want to stay within the worksheet and avoid VBA, use named ranges.

answered Sep 06 '16 at 13:21

Thanks for your help. I adapted your code to fit my exact needs – Frank D. Sep 06 '16 at 15:05

score 0 · Accepted Answer · edited May 23 '17 at 12:22

Solved it: I merged the answer of Jeeped with the answer to this post to create a custom reusable function that does what I need.

It now works by setting =AverageToStDev(CELL) as a value for any cell. Note that CELL must be a single cell containing the AVERAGE() function for this to work.

Function AverageToStDev(MyCell As Range)
    Application.Volatile
    AverageToStDev = Evaluate(Replace(MyCell.Formula, "AVERAGE", "STDEVP", vbTextCompare))
End Function

Excel re-use range that is used by another function

2 Answers2