Help with Brewing Spreadsheet Auto Inputs

[Derek1985]

I am currently working on a calculation tool for my brewing spreadsheet. I have made some great strides streamlining the sheet using Index and Match to auto input my grain analysis values and am now looking to improve my hops and IBU calculations.

Currently I need inputs from this table:

Hop Utilization Table
	OG vs. T	1.030	1.040	1.050	1.060	1.070	1.080	1.090	1.100
	0	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000
	5	0.055	0.050	0.046	0.042	0.038	0.035	0.032	0.029
	10	0.100	0.091	0.084	0.076	0.070	0.064	0.058	0.053
	15	0.137	0.125	0.114	0.105	0.096	0.087	0.080	0.073
	20	0.167	0.153	0.140	0.128	0.117	0.107	0.098	0.089
	25	0.192	0.175	0.160	0.147	0.134	0.122	0.112	0.102
	30	0.212	0.194	0.177	0.162	0.148	0.135	0.124	0.113
	35	0.229	0.209	0.191	0.175	0.160	0.146	0.133	0.122
	40	0.242	0.221	0.202	0.185	0.169	0.155	0.141	0.129
	45	0.253	0.232	0.212	0.194	0.177	0.162	0.148	0.135
	50	0.263	0.240	0.219	0.200	0.183	0.168	0.153	0.140
	55	0.270	0.247	0.226	0.206	0.188	0.172	0.157	0.144
	60	0.276	0.252	0.231	0.211	0.193	0.176	0.161	0.147
	70	0.285	0.261	0.238	0.218	0.199	0.182	0.166	0.152
	80	0.291	0.266	0.243	0.222	0.203	0.186	0.170	0.155
	90	0.295	0.270	0.247	0.226	0.206	0.188	0.172	0.157
	100	0.298	0.272	0.249	0.228	0.208	0.190	0.174	0.159
	110	0.300	0.274	0.251	0.229	0.209	0.191	0.175	0.160
	120	0.301	0.275	0.252	0.230	0.210	0.192	0.176	0.161

<tbody>
</tbody>

in order to proceed with the calculations for IBU.

How can I use formulas to pull values from this sheet based on the dependent parameters? For example:

If someone inputs that they will add hops at say 60 minutes (left most column) and the gravity of the beer is say, 1.090 (upper most row), how can I program a function to auto populate the corresponding value (in this case 0.161)?

Any help would be appreciated.

 

[bbott]

Welcome to the board. Assuming your table starts in A1 and your hops value is in K1 and gravity value is in L1:

=INDEX(B2:I20,MATCH(K1,A2:A20,0),MATCH(L1,B1:I1,0))

 

[CalcSux78]

I copied the table to A4:I23 and set up like this

K1 is a match function: =MATCH(C2,A5:A23,0)
K2 is a match function: =MATCH(E2,B4:I4,0)

So K1 is your row offset, K2 is your Column offset. G2 is looking at a reference from A4

in G2: =OFFSET(A4,K1,K2)
Excel 2010

	A	B	C	D	E	F	G	H	I	J	K
1										R	13
2		OG:	60	T:	1.09	IBU:	0.161			C	7
3
4	OG vs. T	1.03	1.04	1.05	1.06	1.07	1.08	1.09	1.1
5	0	0	0	0	0	0	0	0	0
6	5	0.055	0.05	0.046	0.042	0.038	0.035	0.032	0.029
7	10	0.1	0.091	0.084	0.076	0.07	0.064	0.058	0.053
8	15	0.137	0.125	0.114	0.105	0.096	0.087	0.08	0.073
9	20	0.167	0.153	0.14	0.128	0.117	0.107	0.098	0.089
10	25	0.192	0.175	0.16	0.147	0.134	0.122	0.112	0.102
11	30	0.212	0.194	0.177	0.162	0.148	0.135	0.124	0.113
12	35	0.229	0.209	0.191	0.175	0.16	0.146	0.133	0.122
13	40	0.242	0.221	0.202	0.185	0.169	0.155	0.141	0.129
14	45	0.253	0.232	0.212	0.194	0.177	0.162	0.148	0.135
15	50	0.263	0.24	0.219	0.2	0.183	0.168	0.153	0.14
16	55	0.27	0.247	0.226	0.206	0.188	0.172	0.157	0.144
17	60	0.276	0.252	0.231	0.211	0.193	0.176	0.161	0.147
18	70	0.285	0.261	0.238	0.218	0.199	0.182	0.166	0.152
19	80	0.291	0.266	0.243	0.222	0.203	0.186	0.17	0.155
20	90	0.295	0.27	0.247	0.226	0.206	0.188	0.172	0.157
21	100	0.298	0.272	0.249	0.228	0.208	0.19	0.174	0.159
22	110	0.3	0.274	0.251	0.229	0.209	0.191	0.175	0.16
23	120	0.301	0.275	0.252	0.23	0.21	0.192	0.176	0.161
24

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

 

[Derek1985]

I copied the table to A4:I23 and set up like this

K1 is a match function: =MATCH(C2,A5:A23,0)
K2 is a match function: =MATCH(E2,B4:I4,0)

So K1 is your row offset, K2 is your Column offset. G2 is looking at a reference from A4

in G2: =OFFSET(A4,K1,K2)
Excel 2010

	A	B	C	D	E	F	G	H	I	J	K
1										R	13
2		OG:	60	T:	1.09	IBU:	0.161			C	7
3
4	OG vs. T	1.03	1.04	1.05	1.06	1.07	1.08	1.09	1.1
5	0	0	0	0	0	0	0	0	0
6	5	0.055	0.05	0.046	0.042	0.038	0.035	0.032	0.029
7	10	0.1	0.091	0.084	0.076	0.07	0.064	0.058	0.053
8	15	0.137	0.125	0.114	0.105	0.096	0.087	0.08	0.073
9	20	0.167	0.153	0.14	0.128	0.117	0.107	0.098	0.089
10	25	0.192	0.175	0.16	0.147	0.134	0.122	0.112	0.102
11	30	0.212	0.194	0.177	0.162	0.148	0.135	0.124	0.113
12	35	0.229	0.209	0.191	0.175	0.16	0.146	0.133	0.122
13	40	0.242	0.221	0.202	0.185	0.169	0.155	0.141	0.129
14	45	0.253	0.232	0.212	0.194	0.177	0.162	0.148	0.135
15	50	0.263	0.24	0.219	0.2	0.183	0.168	0.153	0.14
16	55	0.27	0.247	0.226	0.206	0.188	0.172	0.157	0.144
17	60	0.276	0.252	0.231	0.211	0.193	0.176	0.161	0.147
18	70	0.285	0.261	0.238	0.218	0.199	0.182	0.166	0.152
19	80	0.291	0.266	0.243	0.222	0.203	0.186	0.17	0.155
20	90	0.295	0.27	0.247	0.226	0.206	0.188	0.172	0.157
21	100	0.298	0.272	0.249	0.228	0.208	0.19	0.174	0.159
22	110	0.3	0.274	0.251	0.229	0.209	0.191	0.175	0.16
23	120	0.301	0.275	0.252	0.23	0.21	0.192	0.176	0.161
24

<tbody>
</tbody>

Sheet2​

That's awesome and exactly what I was looking for. I'll touch base after I integrate it into the spreadsheet.

 

[bbott]

I copied the table to A4:I23 and set up like this

K1 is a match function: =MATCH(C2,A5:A23,0)
K2 is a match function: =MATCH(E2,B4:I4,0)

So K1 is your row offset, K2 is your Column offset. G2 is looking at a reference from A4

in G2: =OFFSET(A4,K1,K2)
Excel 2010

	A	B	C	D	E	F	G	H	I	J	K
1										R	13
2		OG:	60	T:	1.09	IBU:	0.161			C	7
3
4	OG vs. T	1.03	1.04	1.05	1.06	1.07	1.08	1.09	1.1
5	0	0	0	0	0	0	0	0	0
6	5	0.055	0.05	0.046	0.042	0.038	0.035	0.032	0.029
7	10	0.1	0.091	0.084	0.076	0.07	0.064	0.058	0.053
8	15	0.137	0.125	0.114	0.105	0.096	0.087	0.08	0.073
9	20	0.167	0.153	0.14	0.128	0.117	0.107	0.098	0.089
10	25	0.192	0.175	0.16	0.147	0.134	0.122	0.112	0.102
11	30	0.212	0.194	0.177	0.162	0.148	0.135	0.124	0.113
12	35	0.229	0.209	0.191	0.175	0.16	0.146	0.133	0.122
13	40	0.242	0.221	0.202	0.185	0.169	0.155	0.141	0.129
14	45	0.253	0.232	0.212	0.194	0.177	0.162	0.148	0.135
15	50	0.263	0.24	0.219	0.2	0.183	0.168	0.153	0.14
16	55	0.27	0.247	0.226	0.206	0.188	0.172	0.157	0.144
17	60	0.276	0.252	0.231	0.211	0.193	0.176	0.161	0.147
18	70	0.285	0.261	0.238	0.218	0.199	0.182	0.166	0.152
19	80	0.291	0.266	0.243	0.222	0.203	0.186	0.17	0.155
20	90	0.295	0.27	0.247	0.226	0.206	0.188	0.172	0.157
21	100	0.298	0.272	0.249	0.228	0.208	0.19	0.174	0.159
22	110	0.3	0.274	0.251	0.229	0.209	0.191	0.175	0.16
23	120	0.301	0.275	0.252	0.23	0.21	0.192	0.176	0.161
24

<tbody>
</tbody>

Sheet2​

Just a heads up, you can do this without the helper cells:

=OFFSET(A4,MATCH(C2,A5:A23,0),MATCH(E2,B4:I4,0))

 

[Derek1985]

Worked like a charm. I attempted briefly to install an IF statement that would round my gravity number depending on the user input, i.e. since the numbers in the utilization table are even and in .01 increments, if user input was 1.091, logic test to determine if it needed to be rounded up or down, but in the end just decided to use the ROUND function to round user input up or down.

Another option, albeit probably laborious, would be to encode some sort of interpolation to find values in between those in the table. Right now i'm happy with the amount of accuracy simply rounding applies to the calculations.