Definition of function on one place?

  • Thread starter Thread starter Andreas
  • Start date Start date
A

Andreas

I'm working on a rather large spreadsheet and would like
to use a mathematical formula in several places (around
200). The formula takes values in several different cells
as in data.

Now I would like to know if there is an easy way to make
the spreadsheet flexible, in the sense that I would like
to be able to change the mathematical formula in all the
places to see what implications this leads to.

My idea was that maybe there is a way to write a formula
only once and then use it as reference in all the cells
where I want to use it. Something like defining a
function when programming.

Is this possible? Or is there an other way around my
problem?

BR Andreas
 
There's no built-in way to prototype a formula, though that's been
suggested several times, and is worth sending a request to
(e-mail address removed) (be sure to include "Excel" in the subject line so
that the message gets to the appropriate program mananger.

The easiest way to do this is to create a User Defined Function and edit
that function.

For the simplest formulae, you may be able to use Edit/Replace to
replace function calls or operators.
 
Hi

You can use a named function for this. An example:

You want sum values from clomn to left for 10 rows down, starting from
active row. Select any cell nit in column A, p.e. C3, and then from menu
Insert.Name.Define
Into NamesInWorkbook field insert MyFunc
Into RefersTo field enter
=SUM(Sheet1!B3:B12)

Now use formula =MyFunc in several cells on workbook (I assume you have some
data there available)

After that select some cell (p.e. C3 again), invoke Name builder again and
change the formula for MyFunc, p.e.
=AVERAGE(Sheet1!B3:B12)
(references will be different when another cell is selected!)

The values returned by formulas =MyFunc do change.

Or you define several named functions, like (with cell C3 activated)
MySum=SUM(Sheet1!B3:B12)
MyAverage=AVERAGE(Sheet1!B3:B12)
.....
and define MyFunc as
=MySum
or
=MyAverage
or
....
 
Back
Top