Help with Brewing Spreadsheet Auto Inputs

Derek1985

New Member
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.

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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))

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
ABCDEFGHIJK
1R13
2OG:60T:1.09IBU:0.161C7
3
4OG vs. T1.031.041.051.061.071.081.091.1
5000000000
650.0550.050.0460.0420.0380.0350.0320.029
7100.10.0910.0840.0760.070.0640.0580.053
8150.1370.1250.1140.1050.0960.0870.080.073
9200.1670.1530.140.1280.1170.1070.0980.089
10250.1920.1750.160.1470.1340.1220.1120.102
11300.2120.1940.1770.1620.1480.1350.1240.113
12350.2290.2090.1910.1750.160.1460.1330.122
13400.2420.2210.2020.1850.1690.1550.1410.129
14450.2530.2320.2120.1940.1770.1620.1480.135
15500.2630.240.2190.20.1830.1680.1530.14
16550.270.2470.2260.2060.1880.1720.1570.144
17600.2760.2520.2310.2110.1930.1760.1610.147
18700.2850.2610.2380.2180.1990.1820.1660.152
19800.2910.2660.2430.2220.2030.1860.170.155
20900.2950.270.2470.2260.2060.1880.1720.157
211000.2980.2720.2490.2280.2080.190.1740.159
221100.30.2740.2510.2290.2090.1910.1750.16
231200.3010.2750.2520.230.210.1920.1760.161
24

</tbody>
Sheet2

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
ABCDEFGHIJK
1R13
2OG:60T:1.09IBU:0.161C7
3
4OG vs. T1.031.041.051.061.071.081.091.1
5000000000
650.0550.050.0460.0420.0380.0350.0320.029
7100.10.0910.0840.0760.070.0640.0580.053
8150.1370.1250.1140.1050.0960.0870.080.073
9200.1670.1530.140.1280.1170.1070.0980.089
10250.1920.1750.160.1470.1340.1220.1120.102
11300.2120.1940.1770.1620.1480.1350.1240.113
12350.2290.2090.1910.1750.160.1460.1330.122
13400.2420.2210.2020.1850.1690.1550.1410.129
14450.2530.2320.2120.1940.1770.1620.1480.135
15500.2630.240.2190.20.1830.1680.1530.14
16550.270.2470.2260.2060.1880.1720.1570.144
17600.2760.2520.2310.2110.1930.1760.1610.147
18700.2850.2610.2380.2180.1990.1820.1660.152
19800.2910.2660.2430.2220.2030.1860.170.155
20900.2950.270.2470.2260.2060.1880.1720.157
211000.2980.2720.2490.2280.2080.190.1740.159
221100.30.2740.2510.2290.2090.1910.1750.16
231200.3010.2750.2520.230.210.1920.1760.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.

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
ABCDEFGHIJK
1R13
2OG:60T:1.09IBU:0.161C7
3
4OG vs. T1.031.041.051.061.071.081.091.1
5000000000
650.0550.050.0460.0420.0380.0350.0320.029
7100.10.0910.0840.0760.070.0640.0580.053
8150.1370.1250.1140.1050.0960.0870.080.073
9200.1670.1530.140.1280.1170.1070.0980.089
10250.1920.1750.160.1470.1340.1220.1120.102
11300.2120.1940.1770.1620.1480.1350.1240.113
12350.2290.2090.1910.1750.160.1460.1330.122
13400.2420.2210.2020.1850.1690.1550.1410.129
14450.2530.2320.2120.1940.1770.1620.1480.135
15500.2630.240.2190.20.1830.1680.1530.14
16550.270.2470.2260.2060.1880.1720.1570.144
17600.2760.2520.2310.2110.1930.1760.1610.147
18700.2850.2610.2380.2180.1990.1820.1660.152
19800.2910.2660.2430.2220.2030.1860.170.155
20900.2950.270.2470.2260.2060.1880.1720.157
211000.2980.2720.2490.2280.2080.190.1740.159
221100.30.2740.2510.2290.2090.1910.1750.16
231200.3010.2750.2520.230.210.1920.1760.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))

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.

Replies
4
Views
215
Replies
21
Views
2K

1,202,923
Messages
6,052,581
Members
444,593
Latest member
Smaxls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back