Is there a way to use a number from a table heading in a formula?

[JenniferMurphy]

This table shows some usage statistics relative to how often different letters occur in normal text. The last three columns show how many of each letter a typist will see in an hour of typing at various typing speeds (wmp). As it is, the wpm are hard coded in each cell formula (H7:J13). But that means that if I want to change the wpm, I have to edit the heading and each formula. This is tedious and error prone. Is there any way that I can extract just the number in each heading and use it in the formulas so that I can change the number in the headings and have all of the formulas change, too?

Typing Tutor Adaptive Learning Algorithm.xlsm
	B	C	D	E	F	G	H	I	J
2		e	Relative key		3,000	Characters/page
3		12.30%	Relative key frequency
4
5	R/C	C	D	E	F	G	H	I	J
6	6	Letter	Freq	Comments	Rel Freq vs "e"	#/Page	Chars/Hour at 20 wpm	Chars/Hour at 30 wpm	Chars/Hour at 40 wpm
7	7	e	12.30%	Easy key	1.0000	369	738	1107	1476
8	8	m	2.50%	Difficult key	0.2034	75	150	225	300
9	9	f	2.21%	Home key	0.1798	66	133	199	265
10	10	p	1.92%	Difficult key	0.1560	58	115	173	230
11	11	j	0.15%	Home key	0.0122	5	9	14	18
12	12	q	0.10%	Difficult key	0.0082	3	6	9	12
13	13	z	0.08%	Difficult key	0.0066	2	5	7	10
Relative Key Freq

Cell Formulas
Range	Formula
C3	C3	=XLOOKUP(RefLetter,TblLetters[Letter],TblLetters[Freq])
C5:J5	C5	=col()
F7:F13	F7	=[@Freq]/RefLetterFreq
G7:G13	G7	=CharsPerPage*[@Freq]
H7:H13	H7	=20*60*5*[@Freq]
I7:I13	I7	=30*60*5*[@Freq]
J7:J13	J7	=40*60*5*[@Freq]
B6:B13	B6	=ROW()
D7:D13	D7	=XLOOKUP([@Letter],TblLetters[Letter],TblLetters[Freq])

Named Ranges
Name	Refers To	Cells
A2Z	=Globals!$B$9:$B$34	D7:D13, C3
A2ZFreq	=Globals!$C$9:$C$34	D7:D13, C3
'Relative Key Freq'!CharsPerPage	='Relative Key Freq'!$F$2	G7:G13
LetterFreqTable	=Globals!$B$9:$D$34	D7:D13, C3
'Relative Key Freq'!RefLetter	='Relative Key Freq'!$C$2	C3
'Relative Key Freq'!RefLetterFreq	='Relative Key Freq'!$C$3	F7:F13

Before tables, I was able to put just the number in the heading and then format them to add the text, like this:

Typing Tutor Adaptive Learning Algorithm.xlsm
	D	E	F	G	H
6	Letter	Freq	20 wpm	30 wpm	40 wpm
7	e	12.30%	738	1107	1476
8	m	2.50%	150	225	300
9	f	2.21%	133	199	265
10	p	1.92%	115	173	230
11	j	0.15%	9	14	18
12	q	0.10%	6	9	12
13	z	0.08%	5	7	10
OLD >>>

Cell Formulas
Range	Formula
E7:E13	E7	=XLOOKUP('OLD >>>'!$D7,TblLetters[Letter],TblLetters[Freq])
F7:H13	F7	=F$6*60*5*$E7

Named Ranges
Name	Refers To	Cells
A2Z	=Globals!$B$9:$B$34	E7:E13
A2ZFreq	=Globals!$C$9:$C$34	E7:E13
LetterFreqTable	=Globals!$B$9:$D$34	E7:E13

But that doesn't work with tables.

Is there another way?

Thanks

 

[Automatrix]

You could do a series of helper cells in H4:J4 that extracted the number from the text in H5:J5, and then reference those in the formula.

Book3
	H	I	J
4	20	30	40
5	Chars/Hour at 20 wpm	Chars/Hour at 30 wpm	Chars/Hour at 40 wpm
6
Sheet1

Cell Formulas
Range	Formula
H4:J4	H4	=SUBSTITUTE(SUBSTITUTE(H5,"Chars/Hour at ","")," wpm","")

 

[JenniferMurphy]

You could do a series of helper cells in H4:J4 that extracted the number from the text in H5:J5, and then reference those in the formula.

Book3
	H	I	J
4	20	30	40
5	Chars/Hour at 20 wpm	Chars/Hour at 30 wpm	Chars/Hour at 40 wpm
6
Sheet1

Cell Formulas
Range	Formula
H4:J4	H4	=SUBSTITUTE(SUBSTITUTE(H5,"Chars/Hour at ","")," wpm","")

That's a possibility. I should have mentioned that did try something like that. I would replace "H5" in your expression with a table reference

tablename[[#Headers],[Chars/Hour at 40 wpm]]

.
But it has a couple of problems:

1. Being outside the table, it won't move if I move any of the table columns.
2. If I am going to have a helper cell putside the table, I'd really prefer to make the changes there and have both the formulas and the headings change to match.

Thanks

 

[Automatrix]

That's a possibility. I should have mentioned that did try something like that. I would replace "H5" in your expression with a table reference

tablename[[#Headers],[Chars/Hour at 40 wpm]]

.
But it has a couple of problems:

1. Being outside the table, it won't move if I move any of the table columns.
2. If I am going to have a helper cell putside the table, I'd really prefer to make the changes there and have both the formulas and the headings change to match.

Thanks

1. If you reference the table columns by name rather than H5, it won't matter where they are if you moved them. You can Name the helper cells to make them less sensitive to position (e.g. Helper1 rather than H4).
2. As far as I know, there is no way to put a formula into a table header. But you could call them something generic like "Chars/hour at X wpm", and then have a cell say "X = 20" outside the table.
2a. Except that if you have values X/Y/Z in named cells, and a table with a "Header" column, you can have Power Query do a Pivot Columns to yield column names based on cell values.

Book4
	A	B	C	D
1	X	20
2	Y	30
3	Z	40
4
5	Key	Header value	Header	value
6	e	X	Chars/Hour at 20 wpm	738
7	e	Y	Chars/Hour at 30 wpm	1107
8	e	Z	Chars/Hour at 40 wpm	1476
9
10	Key	Chars/Hour at 20 wpm	Chars/Hour at 30 wpm	Chars/Hour at 40 wpm
11	e	738	1107	1476
Sheet1

Cell Formulas
Range	Formula
C6:C8	C6	="Chars/Hour at "&INDIRECT([@[Header value]])&" wpm"

 

[Eric W]

It would be nice if the table nomenclature had a symbol for "this column" like it does for "this row" (@), but no such luck. There are some options though. Consider:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J
1
2			e	Relative key		3000	Characters/page
3			12.30%	Relative key frequency
4
5
6			Letter	Freq	Comments	Rel Freq vs "e"	#/Page	Chars/Hour at 20 wpm	Chars/Hour at 35 wpm	Chars/Hour at 45 wpm
7			e	12.30%	Easy key	1.0000	369	738	1291.5	1660.5
8			m	2.50%	Difficult key	0.2033	75	150	262.5	337.5
9			f	2.21%	Home key	0.1797	66.3	132.6	232.05	298.35
10			p	1.92%	Difficult key	0.1561	57.6	115.2	201.6	259.2
11			j	0.15%	Home key	0.0122	4.5	9	15.75	20.25
12			q	0.10%	Difficult key	0.0081	3	6	10.5	13.5
13			z	0.08%	Difficult key	0.0065	2.4	4.8	8.4	10.8
Sheet4

Cell Formulas
Range	Formula
C3	C3	=XLOOKUP(RefLetter,TblLetters[Letter],TblLetters[Freq])
F7:F13	F7	=[@Freq]/XLOOKUP($C$2,[Letter],[Freq],0)
G7:G13	G7	=CharsPerPage*[@Freq]
H7:H13	H7	=MID(INDEX(TblLetters[#Headers],COLUMN([@[Chars/Hour at 20 wpm]])-COLUMN([@Letter])+1),15,2)*60*5*[@Freq]
I7:I13	I7	=MID((TblLetters[#Headers] OFFSET([Chars/Hour at 35 wpm],-1,0)),15,2)*60*5*[@Freq]
J7:J13	J7	=45*60*5*[@Freq]

Named Ranges
Name	Refers To	Cells
CharsPerPage	=Sheet4!$F$2	G7:G13
RefLetter	=Sheet4!$C$2	C3, F7:F13

The 20 wpm column has a formula where it figures out the proper column by looking at the column of the current cell, subtracting the column of the first column in the table, and using that as an index into the headers. Then I used MID to get the value out of the heading. So if you change the heading to "Chars/Hour at 25 wpm", the formulas in the column will all adapt.

The 35 wpm column has a shorter formula that does the same thing. But it uses the volatile OFFSET (usually not an issue), and the intersection operator which is rarely used. It works the same as the previous formula.

For both of these I tried using some fancy custom formatting in the cells to avoid the MID, but it seems that table headings don't like that.

The final formula for the 45 wpm column is basically the same as what you had originally. So it's not quite as simple as changing the heading only, but all you have to do is change the heading and the first formula in the column only. All the rest of the formulas will change automatically. So you have to change 2 cells instead of 1, not quite as simple, but you avoid these awkward formulas that reference the headings.

 

[Automatrix]

1. If you reference the table columns by name rather than H5, it won't matter where they are if you moved them. You can Name the helper cells to make them less sensitive to position (e.g. Helper1 rather than H4).
2. As far as I know, there is no way to put a formula into a table header. But you could call them something generic like "Chars/hour at X wpm", and then have a cell say "X = 20" outside the table.
2a. Except that if you have values X/Y/Z in named cells, and a table with a "Header" column, you can have Power Query do a Pivot Columns to yield column names based on cell values.

By the way, you can't tell by the xl2bb copy, but I named B1 as "X" (and B2 as "Y" etc.), for reference in the INDIRECT formula.

 

[JenniferMurphy]

1. If you reference the table columns by name rather than H5, it won't matter where they are if you moved them. You can Name the helper cells to make them less sensitive to position (e.g. Helper1 rather than H4).
2. As far as I know, there is no way to put a formula into a table header. But you could call them something generic like "Chars/hour at X wpm", and then have a cell say "X = 20" outside the table.
2a. Except that if you have values X/Y/Z in named cells, and a table with a "Header" column, you can have Power Query do a Pivot Columns to yield column names based on cell values.

Book4
	A	B	C	D
1	X	20
2	Y	30
3	Z	40
4
5	Key	Header value	Header	value
6	e	X	Chars/Hour at 20 wpm	738
7	e	Y	Chars/Hour at 30 wpm	1107
8	e	Z	Chars/Hour at 40 wpm	1476
9
10	Key	Chars/Hour at 20 wpm	Chars/Hour at 30 wpm	Chars/Hour at 40 wpm
11	e	738	1107	1476
Sheet1

Cell Formulas
Range	Formula
C6:C8	C6	="Chars/Hour at "&INDIRECT([@[Header value]])&" wpm"

Ok, I'll give that a try. Thanks

 

[JenniferMurphy]

It would be nice if the table nomenclature had a symbol for "this column" like it does for "this row" (@), but no such luck. There are some options though. Consider:

Wow. That's some interesting things to play with. That should keep me out of trouble for a bit. Thanks.