How to use a named column in Excel formulas
Simple, at least when using Excel 2010:
- name your column: select full column, enter name
- use column name in formula; Excel will combine the referenced column with the current row to access a single cell.
Using the example from Alex P:
- select column D by clicking the column header containing the "D", enter name "input1" into name field, and press Enter.
- repeat for columns E to F, using "input2" and "input3", respectively.
- Do not define additional names defining names "input1" [...] as in example above!
- use the formula as given in the example above
Attention:
Using named columns this way, you cannot access any other row as the one your formula is in!
At least I'm not aware of the possibility to express something like <ColName>(row+1)...
Suppose I have the following numbers set up in columns D to F in rows 2 to 4:
D E F G
2 10 15 20
3 1 2 3
4 20 30 40
Now suppose I want the value in column D to be known as input1
, column E to be input2
, and column F to input3
:
In Insert > Name > Define...
input1 RefersTo =OFFSET(Sheet1!$D$2,0,0,COUNT(Sheet1!$D:$D),1)
input2 RefersTo =OFFSET(Sheet1!$E$2,0,0,COUNT(Sheet1!$E:$E),1)
input3 RefersTo =OFFSET(Sheet1!$F$2,0,0,COUNT(Sheet1!$F:$F),1)
Now if I write my formula in column G as follows I should get correct answers:
G2 =(10*input1+20*input2+30*input3) // 1000
G3 =(10*input1+20*input2+30*input3) // 140
G5 =(10*input1+20*input2+30*input3) // 2000
I would suggest creating a Table. Select your range A1:H4
, then go to the Tables widget > New > Insert Table with Headers (on Mac). This will mark A2:H4
as body of the table, and A1:H4
as header.
From that, you get:
- Whatever you put into the header column will define the name for this column automatically, e.g.
Count
,Radius
,Density
,Height
- You can then write your formula using
=[@Count]*(10*[@Radius] + 20*[@Density] + 5*[@Height])
- When you change the formula in cell
H2
, Excel will automatically "copy down" this formula to all cells in columnH
. So no more accidental inconsistencies in the formulas. - When you need to add another row, simply click the last cell (in our example
H4
) and hitTab
. Excel adds another row, and also makes sure to "copy down" your formula into the new row. - If you need a total row, add it with the Total Row checkbox in the Tables widget. Excel adds a total row automatically. If you click any cell in the total row, you can change the "total formula" with the "▼▲" button, for example to calculate the Average instead of the Sum of the column.
- If you have a long table and scroll down so that the header is not visible anymore, Excel automatically displays the column header instead of the column names (
Count
instead ofG
for example).
I can really recommend the video You Suck at Excel with Joel Spolsky which explains all of that.