Average IFS with OR function

Fishstats · Nov 21, 2011

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.

T. Valko · Nov 21, 2011

Fishstats said:
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 · Nov 21, 2011

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="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