MODE IF using INDIRECT

[brooksc29]

what type of formula would I need to say

If G2:U80 = "FB"
and the cell below FB says "1"
then find the MODE for the cell below "1"

typically the data is arranged like
row 1 FB
row 2 1
row 3 6
row 4 FB
row 5 1
row 6 A
row 7 FB
row 8 1
row 9 6

the data goes across the row from col G:U in the same pattern

thank you very much for your help! this is a confusing project!

for more information on this project I can either email you my work or you can read another thread where I haven't received a response yet.
http://www.mrexcel.com/forum/showthread.php?t=545394

 

[brooksc29]

this probably won't be formatted correctly, but here goes...

<table border="0" cellpadding="0" cellspacing="0" width="1237"><col style="width: 48pt;" width="64"> <col style="width: 81pt;" width="108"> <col style="width: 80pt;" width="106"> <col style="width: 33pt;" width="44"> <col style="width: 84pt;" width="112"> <col style="width: 26pt;" width="34"> <col style="width: 35pt;" width="47" span="15"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15.75pt;" height="21"> <td class="xl91" style="height: 15.75pt; width: 48pt;" width="64" height="21">DATE</td> <td class="xl92" style="width: 81pt;" width="108">PITCHER</td> <td class="xl71" style="border-left: medium none; width: 80pt;" width="106">BATTER</td> <td class="xl71" style="border-left: medium none; width: 33pt;" width="44">INN</td> <td class="xl71" style="border-left: medium none; width: 84pt;" width="112">CATCHER</td> <td class="xl72" style="width: 26pt;" width="34"> </td> <td class="xl71" style="border-left: medium none; width: 35pt;" width="47">1</td> <td class="xl71" style="border-left: medium none; width: 35pt;" width="47">2</td> <td class="xl71" style="border-left: medium none; width: 35pt;" width="47">3</td> <td class="xl71" style="border-left: medium none; width: 35pt;" width="47">4</td> <td class="xl71" style="border-left: medium none; width: 35pt;" width="47">5</td> <td class="xl71" style="border-left: medium none; width: 35pt;" width="47">6</td> <td class="xl71" style="border-left: medium none; width: 35pt;" width="47">7</td> <td class="xl71" style="border-left: medium none; width: 35pt;" width="47">8</td> <td class="xl71" style="border-left: medium none; width: 35pt;" width="47">9</td> <td class="xl71" style="border-left: medium none; width: 35pt;" width="47">10</td> <td class="xl71" style="border-left: medium none; width: 35pt;" width="47">11</td> <td class="xl71" style="border-left: medium none; width: 35pt;" width="47">12</td> <td class="xl71" style="border-left: medium none; width: 35pt;" width="47">13</td> <td class="xl71" style="border-left: medium none; width: 35pt;" width="47">14</td> <td class="xl71" style="border-left: medium none; width: 35pt;" width="47">15</td> <td class="xl73" style="border-left: medium none; width: 48pt;" width="64">RESULT</td> </tr> <tr style="height: 15pt;" height="20"> <td rowspan="3" class="xl90" style="border-bottom: 1pt solid black; height: 45.75pt; border-top: medium none;" height="61">4/1/2011</td> <td rowspan="3" class="xl84" style="border-bottom: 1pt solid black; border-top: medium none;">JONES</td> <td rowspan="3" class="xl74" style="border-bottom: 1pt solid black; border-top: medium none;">SMITH</td> <td rowspan="3" class="xl77" style="border-bottom: 1pt solid black; border-top: medium none;">1</td> <td rowspan="3" class="xl78" style="border-bottom: 1pt solid black; border-top: medium none;">HILL</td> <td class="xl64" style="border-left: medium none;">PITCH</td> <td class="xl70" style="border-left: medium none;">FB</td> <td class="xl70" style="border-left: medium none;">SL</td> <td class="xl70" style="border-left: medium none;">FB</td> <td class="xl70" style="border-left: medium none;">CB</td> <td class="xl70" style="border-left: medium none;">FB</td> <td class="xl70" style="border-left: medium none;">CH</td> <td class="xl70" style="border-left: medium none;">FB</td> <td class="xl70" style="border-left: medium none;">FB</td> <td class="xl70" style="border-left: medium none;">FB</td> <td class="xl70" style="border-left: medium none;">SL</td> <td class="xl70" style="border-left: medium none;">FB</td> <td class="xl70" style="border-left: medium none;">FB</td> <td class="xl70" style="border-left: medium none;">FB</td> <td class="xl70" style="border-left: medium none;">FB</td> <td class="xl70" style="border-left: medium none;">FB</td> <td rowspan="3" class="xl81" style="border-bottom: 1pt solid black; border-top: medium none;">HIT</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt; border-left: medium none;" height="20">START</td> <td class="xl67" style="border-left: medium none;">1</td> <td class="xl67" style="border-left: medium none;">6</td> <td class="xl67" style="border-left: medium none;">1</td> <td class="xl67" style="border-left: medium none;">1</td> <td class="xl67" style="border-left: medium none;">1</td> <td class="xl67" style="border-left: medium none;">6</td> <td class="xl67" style="border-left: medium none;">6</td> <td class="xl67" style="border-left: medium none;">9</td> <td class="xl67" style="border-left: medium none;">9</td> <td class="xl67" style="border-left: medium none;">1</td> <td class="xl67" style="border-left: medium none;">1</td> <td class="xl67" style="border-left: medium none;">1</td> <td class="xl67" style="border-left: medium none;">1</td> <td class="xl67" style="border-left: medium none;">1</td> <td class="xl67" style="border-left: medium none;">1</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl65" style="height: 15.75pt; border-top: medium none; border-left: medium none;" height="21">FINISH</td> <td class="xl68" style="border-left: medium none;">2</td> <td class="xl68" style="border-left: medium none;">I</td> <td class="xl68" style="border-left: medium none;">1</td> <td class="xl68" style="border-left: medium none;">1</td> <td class="xl68" style="border-left: medium none;">2</td> <td class="xl68" style="border-left: medium none;">6</td> <td class="xl68" style="border-left: medium none;">I</td> <td class="xl68" style="border-left: medium none;">I</td> <td class="xl68" style="border-left: medium none;">I</td> <td class="xl68" style="border-left: medium none;">A</td> <td class="xl68" style="border-left: medium none;">3</td> <td class="xl68" style="border-left: medium none;">1</td> <td class="xl68" style="border-left: medium none;">1</td> <td class="xl68" style="border-left: medium none;">2</td> <td class="xl68" style="border-left: medium none;">3</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl91" style="height: 15.75pt; border-top: medium none;" height="21">DATE</td> <td class="xl92" style="border-top: medium none;">PITCHER</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">BATTER</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">INN</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">CATCHER</td> <td class="xl72" style="border-top: medium none;"> </td> <td class="xl71" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">3</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">4</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">5</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">6</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">7</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">8</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">9</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">10</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">11</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">12</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">13</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">14</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">15</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">RESULT</td> </tr> <tr style="height: 15pt;" height="20"> <td rowspan="3" class="xl87" style="border-bottom: 1pt solid black; height: 45.75pt; border-top: medium none;" height="61"> </td> <td rowspan="3" class="xl84" style="border-bottom: 1pt solid black; border-top: medium none;"> </td> <td rowspan="3" class="xl74" style="border-bottom: 1pt solid black; border-top: medium none;"> </td> <td rowspan="3" class="xl77" style="border-bottom: 1pt solid black; border-top: medium none;"> </td> <td rowspan="3" class="xl78" style="border-bottom: 1pt solid black; border-top: medium none;"> </td> <td class="xl64" style="border-left: medium none;">PITCH</td> <td class="xl66" style="border-left: medium none;"> </td> <td class="xl66" style="border-left: medium none;"> </td> <td class="xl66" style="border-left: medium none;"> </td> <td class="xl66" style="border-left: medium none;"> </td> <td class="xl66" style="border-left: medium none;"> </td> <td class="xl66" style="border-left: medium none;"> </td> <td class="xl66" style="border-left: medium none;"> </td> <td class="xl66" style="border-left: medium none;"> </td> <td class="xl66" style="border-left: medium none;"> </td> <td class="xl66" style="border-left: medium none;"> </td> <td class="xl66" style="border-left: medium none;"> </td> <td class="xl66" style="border-left: medium none;"> </td> <td class="xl66" style="border-left: medium none;"> </td> <td class="xl66" style="border-left: medium none;"> </td> <td class="xl66" style="border-left: medium none;"> </td> <td rowspan="3" class="xl81" style="border-bottom: 1pt solid black; border-top: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt; border-left: medium none;" height="20">START</td> <td class="xl67" style="border-left: medium none;"> </td> <td class="xl67" style="border-left: medium none;"> </td> <td class="xl67" style="border-left: medium none;"> </td> <td class="xl67" style="border-left: medium none;"> </td> <td class="xl67" style="border-left: medium none;"> </td> <td class="xl67" style="border-left: medium none;"> </td> <td class="xl67" style="border-left: medium none;"> </td> <td class="xl67" style="border-left: medium none;"> </td> <td class="xl67" style="border-left: medium none;"> </td> <td class="xl67" style="border-left: medium none;"> </td> <td class="xl67" style="border-left: medium none;"> </td> <td class="xl67" style="border-left: medium none;"> </td> <td class="xl67" style="border-left: medium none;"> </td> <td class="xl67" style="border-left: medium none;"> </td> <td class="xl67" style="border-left: medium none;"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl65" style="height: 15.75pt; border-top: medium none; border-left: medium none;" height="21">FINISH</td> <td class="xl68" style="border-left: medium none;"> </td> <td class="xl68" style="border-left: medium none;"> </td> <td class="xl68" style="border-left: medium none;"> </td> <td class="xl68" style="border-left: medium none;"> </td> <td class="xl68" style="border-left: medium none;"> </td> <td class="xl68" style="border-left: medium none;"> </td> <td class="xl68" style="border-left: medium none;"> </td> <td class="xl68" style="border-left: medium none;"> </td> <td class="xl68" style="border-left: medium none;"> </td> <td class="xl68" style="border-left: medium none;"> </td> <td class="xl68" style="border-left: medium none;"> </td> <td class="xl68" style="border-left: medium none;"> </td> <td class="xl68" style="border-left: medium none;"> </td> <td class="xl68" style="border-left: medium none;"> </td> <td class="xl68" style="border-left: medium none;"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl91" style="height: 15.75pt; border-top: medium none;" height="21">DATE</td> <td class="xl92" style="border-top: medium none;">PITCHER</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">BATTER</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">INN</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">CATCHER</td> <td class="xl72" style="border-top: medium none;"> </td> <td class="xl71" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">3</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">4</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">5</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">6</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">7</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">8</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">9</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">10</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">11</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">12</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">13</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">14</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">15</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">RESULT</td> </tr> <tr style="height: 15pt;" height="20"> <td rowspan="3" class="xl87" style="border-bottom: 1pt solid black; height: 45.75pt; border-top: medium none;" height="61"> </td> <td rowspan="3" class="xl84" style="border-bottom: 1pt solid black; border-top: medium none;"> </td> <td rowspan="3" class="xl74" style="border-bottom: 1pt solid black; border-top: medium none;"> </td> <td rowspan="3" class="xl77" style="border-bottom: 1pt solid black; border-top: medium none;"> </td> <td rowspan="3" class="xl78" style="border-bottom: 1pt solid black; border-top: medium none;"> </td> <td class="xl64" style="border-left: medium none;">PITCH</td> <td class="xl66" style="border-left: medium none;"> </td> <td class="xl66" style="border-left: medium none;"> </td> <td class="xl66" style="border-left: medium none;"> </td> <td class="xl66" style="border-left: medium none;"> </td> <td class="xl66" style="border-left: medium none;"> </td> <td class="xl66" style="border-left: medium none;"> </td> <td class="xl66" style="border-left: medium none;"> </td> <td class="xl66" style="border-left: medium none;"> </td> <td class="xl66" style="border-left: medium none;"> </td> <td class="xl66" style="border-left: medium none;"> </td> <td class="xl66" style="border-left: medium none;"> </td> <td class="xl66" style="border-left: medium none;"> </td> <td class="xl66" style="border-left: medium none;"> </td> <td class="xl66" style="border-left: medium none;"> </td> <td class="xl66" style="border-left: medium none;"> </td> <td rowspan="3" class="xl81" style="border-bottom: 1pt solid black; border-top: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt; border-left: medium none;" height="20">START</td> <td class="xl67" style="border-left: medium none;"> </td> <td class="xl67" style="border-left: medium none;"> </td> <td class="xl67" style="border-left: medium none;"> </td> <td class="xl67" style="border-left: medium none;"> </td> <td class="xl67" style="border-left: medium none;"> </td> <td class="xl67" style="border-left: medium none;"> </td> <td class="xl67" style="border-left: medium none;"> </td> <td class="xl67" style="border-left: medium none;"> </td> <td class="xl67" style="border-left: medium none;"> </td> <td class="xl67" style="border-left: medium none;"> </td> <td class="xl67" style="border-left: medium none;"> </td> <td class="xl67" style="border-left: medium none;"> </td> <td class="xl67" style="border-left: medium none;"> </td> <td class="xl67" style="border-left: medium none;"> </td> <td class="xl67" style="border-left: medium none;"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl65" style="height: 15.75pt; border-top: medium none; border-left: medium none;" height="21">FINISH</td> <td class="xl68" style="border-left: medium none;"> </td> <td class="xl68" style="border-left: medium none;"> </td> <td class="xl68" style="border-left: medium none;"> </td> <td class="xl68" style="border-left: medium none;"> </td> <td class="xl68" style="border-left: medium none;"> </td> <td class="xl68" style="border-left: medium none;"> </td> <td class="xl68" style="border-left: medium none;"> </td> <td class="xl68" style="border-left: medium none;"> </td> <td class="xl68" style="border-left: medium none;"> </td> <td class="xl68" style="border-left: medium none;"> </td> <td class="xl68" style="border-left: medium none;"> </td> <td class="xl68" style="border-left: medium none;"> </td> <td class="xl68" style="border-left: medium none;"> </td> <td class="xl68" style="border-left: medium none;"> </td> <td class="xl68" style="border-left: medium none;"> </td> </tr> </tbody></table>

 

[brooksc29]

sorry, i posted columns A:U.

eventually i am going to need to filter for all sorts of different situations (Pitcher, Batter, Inning, Catcher, and Date)

 

[Aladin Akyurek]

sorry, i posted columns A:U.

eventually i am going to need to filter for all sorts of different situations (Pitcher, Batter, Inning, Catcher, and Date)

Why not post the way I asked?

 

[brooksc29]

my apologies Sir. Here you go

<table border="0" cellpadding="0" cellspacing="0" width="343"><col style="width: 80pt;" width="106"> <col style="width: 33pt;" width="44"> <col style="width: 84pt;" width="112"> <col style="width: 26pt;" width="34"> <col style="width: 35pt;" width="47"> <tbody><tr style="height: 15.75pt;" height="21"> <td class="xl70" style="height: 15.75pt; width: 80pt;" width="106" height="21">BATTER</td> <td class="xl70" style="border-left: medium none; width: 33pt;" width="44">INN</td> <td class="xl70" style="border-left: medium none; width: 84pt;" width="112">CATCHER</td> <td class="xl71" style="width: 26pt;" width="34"> </td> <td class="xl70" style="border-left: medium none; width: 35pt;" width="47">1</td> </tr> <tr style="height: 15pt;" height="20"> <td rowspan="3" class="xl81" style="border-bottom: 1pt solid black; height: 45.75pt; border-top: medium none;" height="61">SMITH</td> <td rowspan="3" class="xl84" style="border-bottom: 1pt solid black; border-top: medium none;">1</td> <td rowspan="3" class="xl85" style="border-bottom: 1pt solid black; border-top: medium none;">HILL</td> <td class="xl64" style="border-left: medium none;">PITCH</td> <td class="xl69" style="border-left: medium none;">FB</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt; border-left: medium none;" height="20">START</td> <td class="xl67" style="border-left: medium none;">1</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl65" style="height: 15.75pt; border-top: medium none; border-left: medium none;" height="21">FINISH</td> <td class="xl68" style="border-left: medium none;">2</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl70" style="height: 15.75pt; border-top: medium none;" height="21">BATTER</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">INN</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">CATCHER</td> <td class="xl71" style="border-top: medium none;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">1</td> </tr> <tr style="height: 15pt;" height="20"> <td rowspan="3" class="xl72" style="border-bottom: 1pt solid black; height: 45.75pt; border-top: medium none;" height="61">HUNTER</td> <td rowspan="3" class="xl75" style="border-bottom: 1pt solid black; border-top: medium none;">1</td> <td rowspan="3" class="xl78" style="border-bottom: 1pt solid black; border-top: medium none;">HILL</td> <td class="xl64" style="border-left: medium none;">PITCH</td> <td class="xl66" style="border-left: medium none;">FB</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt; border-left: medium none;" height="20">START</td> <td class="xl67" style="border-left: medium none;">3</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl65" style="height: 15.75pt; border-top: medium none; border-left: medium none;" height="21">FINISH</td> <td class="xl68" style="border-left: medium none;">1</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl70" style="height: 15.75pt; border-top: medium none;" height="21">BATTER</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">INN</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">CATCHER</td> <td class="xl71" style="border-top: medium none;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">1</td> </tr> <tr style="height: 15pt;" height="20"> <td rowspan="3" class="xl72" style="border-bottom: 1pt solid black; height: 45.75pt; border-top: medium none;" height="61">KERR</td> <td rowspan="3" class="xl75" style="border-bottom: 1pt solid black; border-top: medium none;">1</td> <td rowspan="3" class="xl78" style="border-bottom: 1pt solid black; border-top: medium none;">HILL</td> <td class="xl64" style="border-left: medium none;">PITCH</td> <td class="xl66" style="border-left: medium none;">FB</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt; border-left: medium none;" height="20">START</td> <td class="xl67" style="border-left: medium none;">1</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl65" style="height: 15.75pt; border-top: medium none; border-left: medium none;" height="21">FINISH</td> <td class="xl68" style="border-left: medium none;">1</td> </tr> </tbody></table>

 

[brooksc29]

maybe this is easier. just not sure the cleanest way to post on here

<table border="0" cellpadding="0" cellspacing="0" width="153"><col style="width: 80pt;" width="106"> <col style="width: 35pt;" width="47"> <tbody><tr style="height: 15.75pt;" height="21"> <td class="xl72" style="height: 15.75pt; width: 80pt;" width="106" height="21">BATTER</td> <td class="xl72" style="border-left: medium none; width: 35pt;" width="47">1</td> </tr> <tr style="height: 15pt;" height="20"> <td rowspan="3" class="xl83" style="border-bottom: 1pt solid black; height: 45.75pt; border-top: medium none;" height="61">SMITH</td> <td class="xl71" style="border-left: medium none;">FB</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; border-left: medium none;" height="20">1</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl70" style="height: 15.75pt; border-left: medium none;" height="21">2</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl72" style="height: 15.75pt; border-top: medium none;" height="21">BATTER</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">1</td> </tr> <tr style="height: 15pt;" height="20"> <td rowspan="3" class="xl74" style="border-bottom: 1pt solid black; height: 45.75pt; border-top: medium none;" height="61">HUNTER</td> <td class="xl68" style="border-left: medium none;">FB</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; border-left: medium none;" height="20">3</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl70" style="height: 15.75pt; border-left: medium none;" height="21">1</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl72" style="height: 15.75pt; border-top: medium none;" height="21">BATTER</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">1</td> </tr> <tr style="height: 15pt;" height="20"> <td rowspan="3" class="xl74" style="border-bottom: 1pt solid black; height: 45.75pt; border-top: medium none;" height="61">KERR</td> <td class="xl68" style="border-left: medium none;">FB</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; border-left: medium none;" height="20">1</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl70" style="height: 15.75pt; border-left: medium none;" height="21">1</td> </tr> </tbody></table>

 

[Aladin Akyurek]

maybe this is easier. just not sure the cleanest way to post on here

<TABLE border=0 cellSpacing=0 cellPadding=0 width=153><COLGROUP><COL style="WIDTH: 80pt" width=106><COL style="WIDTH: 35pt" width=47><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="WIDTH: 80pt; HEIGHT: 15.75pt" class=xl72 height=21 width=106>BATTER</TD><TD style="BORDER-LEFT: medium none; WIDTH: 35pt" class=xl72 width=47>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 1pt solid; HEIGHT: 45.75pt; BORDER-TOP: medium none" class=xl83 height=61 rowSpan=3>SMITH</TD><TD style="BORDER-LEFT: medium none" class=xl71>FB</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-LEFT: medium none; HEIGHT: 15pt" class=xl69 height=20>1</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-LEFT: medium none; HEIGHT: 15.75pt" class=xl70 height=21>2</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="HEIGHT: 15.75pt; BORDER-TOP: medium none" class=xl72 height=21>BATTER</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl72>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 1pt solid; HEIGHT: 45.75pt; BORDER-TOP: medium none" class=xl74 height=61 rowSpan=3>HUNTER</TD><TD style="BORDER-LEFT: medium none" class=xl68>FB</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-LEFT: medium none; HEIGHT: 15pt" class=xl69 height=20>3</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-LEFT: medium none; HEIGHT: 15.75pt" class=xl70 height=21>1</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="HEIGHT: 15.75pt; BORDER-TOP: medium none" class=xl72 height=21>BATTER</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl72>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 1pt solid; HEIGHT: 45.75pt; BORDER-TOP: medium none" class=xl74 height=61 rowSpan=3>KERR</TD><TD style="BORDER-LEFT: medium none" class=xl68>FB</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-LEFT: medium none; HEIGHT: 15pt" class=xl69 height=20>1</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-LEFT: medium none; HEIGHT: 15.75pt" class=xl70 height=21>1</TD></TR></TBODY></TABLE>

That's better...

Compared to references this formula

=MODE(IF(G2:U78="FB",IF(G3:U79=1,IF(ISNUMBER(G4:U80),G4:U80))))

is built upon, am I correct to conclude that names are on the same line/row as 1, put otherwise, immediately underneath the FB row?

 

[brooksc29]

in column C the names are in row 2 (6 & 10 as well in this sample). those cells in column C are grouped for rows 2-4

so the first cell reference for a name would be C2

 

[brooksc29]

argh!

I am really sorry, but I've just been told that instead of there being numbers underneath the FB, there will now be letters. So that kind of renders the MODE as moot.

would an INDEX MATCH formula work instead?

=MODE(IF(G2:U78="FB",IF(G3:U79=1,IF(ISNUMBER(G4:U80),G4:U80))))

I'm thinking of something like this?
=INDEX(G2:U80,MATCH(MAX(IF(G2:U80="FB",IF(G3:U80="A",G4:U80)))),0))

 

[Aladin Akyurek]

in column C the names are in row 2 (6 & 10 as well in this sample). those cells in column C are grouped for rows 2-4

so the first cell reference for a name would be C2

I'm trying to discover the structural relation between a name, FB, and condition 1...

So we have:

=MODE(IF(G2:U78="FB",IF(G3:U79=1,IF(ISNUMBER(G4:U80),G4:U80))))

which tests whether G2 is FB, G3 is 1, G4 is a number.

Does the following with CSE confirmed formula...

=MODE(IF(C2:C78="SMITH",IF(G2:U78="FB",IF(G3:U79=1,IF(ISNUMBER(G4:U80),G4:U80)))))

then make sense?

 

[Aladin Akyurek]

argh!

I am really sorry, but I've just been told that instead of there being numbers underneath the FB, there will now be letters. So that kind of renders the MODE as moot.

would an INDEX MATCH formula work instead?

=MODE(IF(G2:U78="FB",IF(G3:U79=1,IF(ISNUMBER(G4:U80),G4:U80))))

I'm thinking of something like this?
=INDEX(G2:U80,MATCH(MAX(IF(G2:U80="FB",IF(G3:U80="A",G4:U80)))),0))

=MODE(IF(G2:U78="FB",IF(G3:U79="A",IF(ISNUMBER(G4:U80),G4:U80))))

would still yield a mode.