Hlookup and average

[aintelligentthug]

I am a newbie needing help with hlookup and average.
<table x:str="" style="border-collapse: collapse; width: 240pt;" width="320" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" span="5" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; width: 48pt;" x:num="38778" width="64" align="right" height="17">2-Mar</td> <td class="xl22" style="width: 48pt;" width="64">
</td> <td class="xl23" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl22" style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Fast/Slow</td> <td class="xl22">Flow</td> <td class="xl23">Flow</td> <td class="xl24">Flow</td> <td class="xl22">Flow</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Picker</td> <td class="xl22">Brian Roberts</td> <td class="xl23" x:str="Cory Greene ">Cory Greene </td> <td class="xl24" x:str="John Miller ">John Miller </td> <td class="xl22" x:str="joshua murphy ">joshua murphy </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Picks</td> <td class="xl22">
</td> <td class="xl23" x:num="" align="right">2792</td> <td class="xl24" x:num="" align="right">2690</td> <td class="xl22" x:num="" align="right">919</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Time</td> <td class="xl22">
</td> <td class="xl23" x:num="" align="right">344.55</td> <td class="xl24" x:num="" align="right">337.12</td> <td class="xl22" x:num="" align="right">116.02</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">Bottle Rate</td> <td class="xl23" x:num="8.1029999999999998" align="right">8.103</td> <td class="xl24" x:num="7.9790000000000001" align="right">7.979</td> <td class="xl22" x:num="7.9210000000000003" align="right">7.921</td> </tr> </tbody></table>
with 2-march being A1, Flow/slow being A2, Brian being B3 and so forth. This data is repeated for each day of the month. I would like to be able to look up and average the bottle rate for each name for the entire month. Any help is greatly appreciated.

 

[mikerickson]

What cell and sheet has 3-Mar?

 

[aintelligentthug]

sheet 1 cell A7

 

[mikerickson]

maybe something like this
<table border=1 cellspacing = 0 bgcolor="#ffffff"><tr bgcolor = "#aaaaaa"><td> <td align=center width=99><b>A</b><td align=center width=99><b>B</b><td align=center width=99><b>C</b><td align=center width=99><b>D</b><td align=center width=99><b>E</b><td align=center width=99><b>F</b><td align=center width=99><b>G</b><td align=center width=99><b>H</b><td align=center width=99><b>I</b>
<tr><td align=center bgcolor="#aaaaaa"><b>1</b><td bgcolor="#FFFFFF" > <FONT color="#000000">2-Mar</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">name</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Brian Roberts</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Cory Greene</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">John Miller</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>2</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Fast/Slow</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Flow</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Flow</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Flow</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Total picks</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">9070</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">3090</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2757</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>3</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Brian Roberts</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Cory Greene</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">John Miller</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Total time</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">1124.55</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">397.12</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">348.06</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>4</b><td bgcolor="#FFFFFF" > <FONT color="#000000">picks</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2792</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2690</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">919</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Combined Rate</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">8.065</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">7.781</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">7.921</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>5</b><td bgcolor="#FFFFFF" > <FONT color="#000000">time</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">344.55</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">337.12</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">116.02</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>6</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Bottle Rate</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">8.103</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">7.979</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">7.921</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>7</b><td bgcolor="#FFFFFF" > <FONT color="#000000">3-Mar</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>8</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Fast/Slow</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Flow</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Flow</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Flow</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>9</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Brian Roberts</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Cory Greene</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">John Miller</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>10</b><td bgcolor="#FFFFFF" > <FONT color="#000000">picks</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">3000</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">200</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">919</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>11</b><td bgcolor="#FFFFFF" > <FONT color="#000000">time</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">380</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">30</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">116.02</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>12</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Bottle Rate</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">7.895</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">6.667</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">7.921</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>13</b><td bgcolor="#FFFFFF" > <FONT color="#000000">4-Mar</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>14</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Fast/Slow</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Flow</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Flow</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Flow</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>15</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">John Miller</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Brian Roberts</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Cory Greene</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>16</b><td bgcolor="#FFFFFF" > <FONT color="#000000">picks</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">919</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">3278</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">200</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>17</b><td bgcolor="#FFFFFF" > <FONT color="#000000">time</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">116.02</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">400</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">30</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>18</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Bottle Rate</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">7.921</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">8.195</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">6.667</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr></table><table border=1 cellspacing = 0 bgcolor="#ddedcc">
<tr><td colspan=3 align="center">Formulas in this range: </tr>
<tr><td align=center>Range with same formula<td align=center>Cell:<td align=center>holds Formula:</tr>
<tr><td>G2:I2<td align=center>G2 <td align = left >=SUMIF($B$3:$D$100,G$1,$B$4:$D$101)</tr>
<tr><td>G3:I3<td align=center>G3 <td align = left >=SUMIF($B$3:$D$100,G$1,$B$5:$D$102)</tr>
<tr><td>G4:I4<td align=center>G4 <td align = left >=G2/G3</tr></table>

 

[aintelligentthug]

Great, with the original data extending to the N column, I can maybe set this table in another sheet. I then maybe can use a drop down list with data validation to pull those values. Also will this formula ignore the blanks? but it is also important for me to keep emplyee names in same column.

 

[mikerickson]

If the Picks for a particular day is blank, it will be counted as 0. As will blank Time for a particular day. Which produces the correct overall rate.

 

[aintelligentthug]

great I really appreciate all of your help!!!!!