How to make two formulas reference the same cells in Excel?

I have one row that uses =MEAN(A1:A15), and another one that uses =SUM(A1:A15). But many times I have to alter this range to, let's say, A1:A13;A15 in both cells. I would like to alter the range in one place and have both formulas to use the same updated range.

Jader Dias

Posted 2011-07-28T13:23:44.730

Reputation: 13 660

Answers

What you can do is use the INDIRECT formula along with another cell where you specify your range. The INDIRECT formula allows you to return a cell reference or range based on a text string. For example, you can set a cell equal to =INDIRECT("A2") and it will show the value of cell A2.

As for how this pertains to your question, if for example we place the text string A1:A15 in cell B1, you can then get the sum of that range with this formula: =SUM(INDIRECT(B1)). Modifying the range in cell B1 will then modify the computed sum accordingly, and you can also perform other formulas with that, like =MEAN(INDIRECT(B1)).

Breakthrough

Posted 2011-07-28T13:23:44.730

Reputation: 32 927

Now I realized I need the inverse function too. Is there any STRINGIFY(A1:A15) which would return "A1:A15"? – Jader Dias – 2011-07-28T13:54:06.003

1You can set a cell equal to ="A1:A15" if you'd like. You can also use the & operator to join strings, so if for example if you wanted to return the range of all entries in the A column, you could make the string as ="A1:A" & COUNTA(A:A). Note that you could also just place that right as the argument of the INDIRECT formula, instead of referencing another cell. – Breakthrough – 2011-07-28T13:56:31.317

Thanks, but I mean http://superuser.com/questions/316224/how-to-make-many-column-formulas-ignore-the-same-lines-in-excel

– Jader Dias – 2011-07-28T14:05:13.260

@Jader Dias also posted an answer there for you. – Breakthrough – 2011-07-28T14:52:44.077

You can use a named range to do this, and it's really the recommended way. That is where you give a name to a range of cells, and then you use that name in any formulas. You can later go back and update the range of cells that the name applies to. (You can also do more sophisticated things like having the range automatically adjust in size when you add rows at the end.)

Creating and applying named ranges is under Formula > Named Cells > Name a Range.

More details in this tutorial: http://www.homeandlearn.co.uk/excel2007/excel2007s7p6.html

Scott McIntyre

Posted 2011-07-28T13:23:44.730

Reputation: 241