Question about repeated usage of a formula

[G.R. Toro]

Here is my question (BTW, I am using Excel 2003):

I have a table of values, and I have given each column in the table a name
(created with Insert|Name|Define). For instance, these names may be
something like

Price | Weight | Volume | Age | etc., beginning at address A10
100 | 3 | 10 | 6 |
20 | 5 | 7 | 1 |


Now, I want to create a new column with values derived from the values in
this table. The difficulty is that I would like to have the formula (for
instance, =Weight/Volume) in one place only (say, cell B1), in such as way
that I can change the formula in B1 and cause the change to propagate to the
entire new column of my table.

In essence, what I would like is a function that says: evaluate the formula
in $B$1 as if that formula had been written in the current cell. It's
something like =offset($b$1,0,0) or =indirect("$B$1"), but for formulas
involving Names.

Any suggestions would be greatly appreciated.

Thanks,

Gabriel

 

[Bernard Liengme]

You need the formula to be =B10/C10 without names
You will have to type this rather that build it by pointing since pointing
always picks up names
Then you can copy it down the column
Are you using the name for something else? If not then Insert | Names |
Define | Delete
best wishes

 

[G.R. Toro]

Bernard,

Thank you. I was hoping for a more elegant answer. I am trying to avoid
having to copy anything (I'll try to do it behind the user's back with vba
and a worksheet event). Also, the use of Names makes the process more user
friendly.

Thanks,

Gabriel

 

[Charles Williams]

Assuming that Weight is something like $B$11:$B$1000 and Volume
$C$11:$C$1000 then in E10 put =Weight/Volume and copy down.
This is using Implicit Intersection: Excel uses the current row from Weight
and Volume

If you want to do the same thing but on different rows for example starting
at E1 then select E1:E990 and enter =Weight/Volume using Control/Shift/Enter
so that you are creating a multi-row array formula.

regards
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[G.R. Toro]

Charles,

Thank you. What I am trying to avoid is the manual copying and pasting
every time the user changes the formula in B1.

Gabriel

 

[Charles Williams]

Gabriel,

here are 3 suggestions:

1) the user changes the formula in E11 to Weight/Volume and then
doubleclicks the little black square at the bottom right of the cell to
automatically fill down

2) Use VBA in the Worksheet Change event to do the copy paste

3) Copy the EVAL function you can find at
http://www.decisionmodels.com/calcsecretsh.htm
into a general VBA module (Atl-F11 --> Insert-->Module)

in A1 enter '=Weight/Volume (the formula as a string)

in E11 enter =INDEX(EVAL($A$1),ROW()-10) and copy (or fill) down


Personally I would use solution 1!

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com