Average IFS with OR function

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

T. Valko

Well-known Member
I am trying to find the average length of animals that are in length bins. I need to do this for individual gears but then also combinations of different gears. For example I need the average length of animals 50-74mm, captured using either of gear 1 or 2. There are 4 different gear types. My data is arranged so that Column B is Gear type, D is length, E is also length (same numbers as column D). The equation I have tried is =AVERAGEIFS(\$D:\$D,\$D:\$D,">24",\$E:\$E,"<50",\$B:\$B,OR(\$B:\$B = "GLCWC",B:B = "Seine",B:B = "XLTRAP")). This equation gives the average for all individuals rather than just the gears “GLCWC” and “XLTRAP”. Any help on fixing this equation would be greatly appreciated.
I don't understand your attempted formula.

If column E contains the same numbers as column D then why are you testing each of those columns for different conditions?

Can you post some data and tell us what result you expect?

Fishstats

New Member

<table class="MsoNormalTable" style="width:246.45pt;margin-left:4.65pt;border-collapse:collapse;mso-yfti-tbllook: 1184;mso-padding-alt:0in 5.4pt 0in 5.4pt" width="329" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes;height:15.0pt"> <td style="width:54.45pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="73" nowrap="nowrap"> Site
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap"> Gear
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap"> Species
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap"> Length
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap"> Length2
</td> </tr> <tr style="mso-yfti-irow:1;height:15.0pt"> <td style="width:54.45pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="73" nowrap="nowrap"> GREEN
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap"> GLCWC
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap"> X
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">
25
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">
25
</td> </tr> <tr style="mso-yfti-irow:2;height:15.0pt"> <td style="width:54.45pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="73" nowrap="nowrap"> GREEN
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap"> XLTRAP
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap"> X
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">
26
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">
26
</td> </tr> <tr style="mso-yfti-irow:3;height:15.0pt"> <td style="width:54.45pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="73" nowrap="nowrap"> GREEN
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap"> XLTRAP
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap"> Z
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">
25
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">
25
</td> </tr> <tr style="mso-yfti-irow:4;height:15.0pt"> <td style="width:54.45pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="73" nowrap="nowrap"> GREEN
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap"> TRAP
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap"> Z
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">
35
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">
35
</td> </tr> <tr style="mso-yfti-irow:5;height:15.0pt"> <td style="width:54.45pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="73" nowrap="nowrap"> GREEN
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap"> TRAP
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap"> Z
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">
45
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">
45
</td> </tr> <tr style="mso-yfti-irow:6;mso-yfti-lastrow:yes;height:15.0pt"> <td style="width:54.45pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="73" nowrap="nowrap"> GREEN
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap"> GLCWC
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap"> A
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">
49
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">
49
</td> </tr> </tbody></table>

T. Valko

Well-known Member
I am trying to calculate the average of individuals between 25 and 49 mm. I was unsure how to do this if I only used one column of data so I used two (Length and Length2). This seems to work fine as it finds the average length of all individuals between that length range. My current problem is when I use the OR statement where I want to calculate the average species between that range but also incorporate which gear type they were captured with. For example I want the average length of individuals captured using either the XLTRAP or the GLCWC that are between 25 and 49 mm. So it should give me the average 31.25 (that is the average of 4 individuals highlighted in yellow).

<TABLE class=MsoNormalTable style="MARGIN-LEFT: 4.65pt; WIDTH: 246.45pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 5.4pt 0in 5.4pt" cellSpacing=0 cellPadding=0 width=329 border=0><TBODY><TR style="HEIGHT: 15pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; WIDTH: 54.45pt; PADDING-TOP: 0in; HEIGHT: 15pt" vAlign=bottom noWrap width=73>Site

25

25

26

26

25

25

35

35

45

45

49

49

</TD></TR></TBODY></TABLE>
Try this...

<b>Sheet1</b><br /><br /><table border="0" cellspacing="0" cellpadding="0" style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:57px;" /><col style="width:57px;" /><col style="width:58px;" /><col style="width:52px;" /><col style="width:60px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style=" border-style:solid; border-width:1px; border-color:#000000; ">Site</td><td style=" border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Gear</td><td style=" border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Species</td><td style=" border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Length</td><td style=" border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Length2</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">GREEN</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">GLCWC</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">X</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">25</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">25</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">GREEN</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">XLTRAP</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">X</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">26</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">26</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">GREEN</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">XLTRAP</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Z</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">25</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">25</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">GREEN</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">TRAP</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Z</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">35</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">35</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">GREEN</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">TRAP</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Z</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">45</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">45</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">GREEN</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">GLCWC</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">A</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">49</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">49</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">XLTRAP</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">GLCWC</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">31.25</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr></table> <br /><br />
This array formula** entered in C10:

=AVERAGE(IF(ISNUMBER(MATCH(B2:B7,A10:B10,0)),D2:D7))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Fishstats

New Member

Thanks that's great it works. Now how do I incorporate calculating that for only lengths in that 25-49 size range. I have 32000 lines of data and lengths ranging from 12-700. I need the averages for 25 mm bins using the combinations of gears. Thanks again.

T. Valko

Well-known Member
Thanks that's great it works. Now how do I incorporate calculating that for only lengths in that 25-49 size range. I have 32000 lines of data and lengths ranging from 12-700. I need the averages for 25 mm bins using the combinations of gears. Thanks again.
Try this...

<b>Sheet1</b><br /><br /><table border="0" cellspacing="0" cellpadding="0" style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:57px;" /><col style="width:57px;" /><col style="width:58px;" /><col style="width:52px;" /><col style="width:60px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style=" border-style:solid; border-width:1px; border-color:#000000; ">Site</td><td style=" border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Gear</td><td style=" border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Species</td><td style=" border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Length</td><td style=" border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Length2</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">GREEN</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">GLCWC</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">X</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">25</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">25</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">GREEN</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">XLTRAP</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">X</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">26</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">26</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">GREEN</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">XLTRAP</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Z</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">25</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">25</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">GREEN</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">TRAP</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Z</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">35</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">35</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">GREEN</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">TRAP</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Z</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">45</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">45</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">GREEN</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">GLCWC</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">A</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">49</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">49</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Gear</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">XLTRAP</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">GLCWC</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Length</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">25</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">49</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">AVG</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">31.25</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr></table> <br /><br />
This array formula** entered in B11:

=AVERAGE(IF(ISNUMBER(MATCH(B2:B7,B9:C9,0)),IF(D2:D7>=B10,IF(D2:D7<=C10,D2:D7))))

Fishstats

New Member
Thank you!!!!
Saved me alot of time and frustration.

T. Valko

Well-known Member
Thank you!!!!
Saved me alot of time and frustration.
You're welcome. Thanks for the feedback!

Replies
1
Views
472
Replies
9
Views
992
Replies
1
Views
596
Replies
6
Views
598
Replies
3
Views
869

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,193
Messages
5,768,766
Members
425,492
Latest member
blueexcel123

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