AverageIf (average one column if the value in another column meets criteria)

[sdoppke]

Hi everyone,i have found a lot on this topic but none exactly for what i am trying to do.

<TABLE style="WIDTH: 104pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=138><COLGROUP><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" span=2 width=69><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d8d8d8 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #92d050; WIDTH: 52pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=17 width=69> Column A</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: lime; WIDTH: 52pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 width=69 align=right>Column R</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d8d8d8 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #92d050; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=17> 182 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: lime; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 align=right>194</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d8d8d8 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #92d050; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=17> 170 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: lime; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 align=right>170</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d8d8d8 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #92d050; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=17> 157 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: lime; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 align=right>171</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d8d8d8 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #92d050; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=17> 149 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: lime; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 align=right>145</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d8d8d8 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #92d050; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=17> 205 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: lime; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 align=right>134</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d8d8d8 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #92d050; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=17> 180 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: lime; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 align=right>167</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d8d8d8 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #92d050; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=17> 164 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: lime; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 align=right>208</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d8d8d8 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #92d050; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=17> 157 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: lime; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 align=right>144</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d8d8d8 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #92d050; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=17> 157 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: lime; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 align=right>146</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d8d8d8 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #92d050; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=17> 180 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: lime; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 align=right>154</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d8d8d8 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #92d050; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=17> 165 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: lime; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 align=right>166</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d8d8d8 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #92d050; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=17> 183 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: lime; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 align=right>183</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d8d8d8 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #92d050; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=17> 151 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: lime; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 align=right>201</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d8d8d8 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #92d050; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=17> 146 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: lime; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 align=right>124</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d8d8d8 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #92d050; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=17> 140 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: lime; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 align=right>196</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d8d8d8 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #92d050; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=17> 146 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: lime; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 align=right>175</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d8d8d8 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #92d050; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=17> 144 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: lime; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 align=right>175</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d8d8d8 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #92d050; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=17> 144 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: lime; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 align=right>199</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d8d8d8 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #92d050; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=17> 244 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: lime; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 align=right>193</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d8d8d8 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #92d050; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=17> 205 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: lime; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 align=right>206</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d8d8d8 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #92d050; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=17> 140 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: lime; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 align=right>148</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d8d8d8 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #92d050; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=17> 180 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: lime; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 align=right>187</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d8d8d8 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #92d050; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=17> 173 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: lime; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 align=right>195</TD></TR></TBODY></TABLE>

Based on the columns A and R above. I am trying to find the average in column A only in regards to the values when the same row in column R is above (>176)

Thanks in advance for any help

sd

 

[VoG]

Try

=AVERAGEIF(A1:A200,R1:R200,">200")

 

[sdoppke]

Try

=AVERAGEIF(A1:A200,R1:R200,">200")

Thanks VoG!! gives me an error. Doesnt seem to like the = sign

sd

Tried this but no luck also

=AVERAGEIF(R10:R5005,"<176",F10:F5000)

cause the average says its 217 (which is above 176?)

sd

 

[Yahya]

i think peter mean this formula
=AVERAGEIF(R1:R200,">200",A1:A200)


and also you can test this array formula
=AVERAGE(IF(R1:R23>200,A1:A23))

array formula must enter by
Ctrl+shift+Enter

 

[sdoppke]

i think peter mean this formula
=AVERAGEIF(R1:R200,">200",A1:A200)


and also you can test this array formula
=AVERAGE(IF(R1:R23>200,A1:A23))

array formula must enter by
Ctrl+shift+Enter

=AVERAGEIF(R10:R5004,"<176",F10:F5000)

The first one gives me an average above 217. (I dont understand that)

This one give me a Div/0 erro (there are 0's and N/A's etc..)

=AVERAGE(IF(F10:F5000<176,R10:R5000))

thanks for any help.

sd

 

[Yahya]

the range must be equal
=AVERAGEIF(R10:R5004,"<176",F10:F5000)
=AVERAGEIF(R10:R5000,"<176",F10:F5000)

the second one
must be entered with Press Ctrl Then Shift Then Enter

test it please

 

[sdoppke]

the range must be equal
=AVERAGEIF(R10:R5004,"<176",F10:F5000)
=AVERAGEIF(R10:R5000,"<176",F10:F5000)

the second one
must be entered with Press Ctrl Then Shift Then Enter

test it please

Thanks i didnt catch that first mistake.

on the second i did use that method (strl shift enter) thinks it cause of the N/A's and the Div/0's?

sd

 

[Yahya]

you are welcome
this is the two formula
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>F</th><th>R</th><th>S</th><th>T</th><th>U</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;border-bottom: 1px solid black;background-color: #92D050;;">182</td><td style="text-align: right;background-color: #00FF00;;">194</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">170</td><td style="text-align: right;background-color: #00FF00;;">170</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">161.2308</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">157</td><td style="text-align: right;background-color: #00FF00;;">171</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">161.2308</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">149</td><td style="text-align: right;background-color: #00FF00;;">145</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">205</td><td style="text-align: right;background-color: #00FF00;;">134</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">180</td><td style="text-align: right;background-color: #00FF00;;">167</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">164</td><td style="text-align: right;background-color: #00FF00;;">208</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">157</td><td style="text-align: right;background-color: #00FF00;;">144</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">157</td><td style="text-align: right;background-color: #00FF00;;">146</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">180</td><td style="text-align: right;background-color: #00FF00;;">154</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">165</td><td style="text-align: right;background-color: #00FF00;;">166</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">183</td><td style="text-align: right;background-color: #00FF00;;">183</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">151</td><td style="text-align: right;background-color: #00FF00;;">201</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">146</td><td style="text-align: right;background-color: #00FF00;;">124</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">140</td><td style="text-align: right;background-color: #00FF00;;">196</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">146</td><td style="text-align: right;background-color: #00FF00;;">175</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">144</td><td style="text-align: right;background-color: #00FF00;;">175</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">144</td><td style="text-align: right;background-color: #00FF00;;">199</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">244</td><td style="text-align: right;background-color: #00FF00;;">193</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">205</td><td style="text-align: right;background-color: #00FF00;;">206</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">140</td><td style="text-align: right;background-color: #00FF00;;">148</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">180</td><td style="text-align: right;background-color: #00FF00;;">187</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">173</td><td style="text-align: right;background-color: #00FF00;;">195</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">U11</th><td style="text-align:left">=AVERAGEIF(<font color="Blue">R10:R209,"<176",F10:F209</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">U12</th><td style="text-align:left">{=AVERAGE(<font color="Blue">IF(<font color="Red">R10:R32<176,F10:F32</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[sdoppke]

you are welcome
this is the two formula
Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>F</TH><TH>R</TH><TH>S</TH><TH>T</TH><TH>U</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #92d050">182</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00">194</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #92d050; BORDER-TOP: black 1px solid">170</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00">170</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">161.2308</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #92d050; BORDER-TOP: black 1px solid">157</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00">171</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">161.2308</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #92d050; BORDER-TOP: black 1px solid">149</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00">145</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #92d050; BORDER-TOP: black 1px solid">205</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00">134</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #92d050; BORDER-TOP: black 1px solid">180</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00">167</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #92d050; BORDER-TOP: black 1px solid">164</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00">208</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #92d050; BORDER-TOP: black 1px solid">157</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00">144</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #92d050; BORDER-TOP: black 1px solid">157</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00">146</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #92d050; BORDER-TOP: black 1px solid">180</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00">154</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #92d050; BORDER-TOP: black 1px solid">165</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00">166</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">21</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #92d050; BORDER-TOP: black 1px solid">183</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00">183</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">22</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #92d050; BORDER-TOP: black 1px solid">151</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00">201</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">23</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #92d050; BORDER-TOP: black 1px solid">146</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00">124</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">24</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #92d050; BORDER-TOP: black 1px solid">140</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00">196</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">25</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #92d050; BORDER-TOP: black 1px solid">146</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00">175</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">26</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #92d050; BORDER-TOP: black 1px solid">144</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00">175</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">27</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #92d050; BORDER-TOP: black 1px solid">144</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00">199</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">28</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #92d050; BORDER-TOP: black 1px solid">244</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00">193</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">29</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #92d050; BORDER-TOP: black 1px solid">205</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00">206</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">30</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #92d050; BORDER-TOP: black 1px solid">140</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00">148</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">31</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #92d050; BORDER-TOP: black 1px solid">180</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00">187</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">32</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #92d050; BORDER-TOP: black 1px solid">173</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00">195</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>

Sheet1​

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>U11</TH><TD style="TEXT-ALIGN: left">=AVERAGEIF(R10:R209,"<176",F10:F209)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>U12</TH><TD style="TEXT-ALIGN: left">{=AVERAGE(IF(R10:R32<176,F10:F32))}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
</TD></TR></TBODY></TABLE>

I completely understand now, thanks for sticking with me . Can you help me to adjust the <176 to < (a cell value) i.e. <B2 (which doesnt work)

 

[Yahya]

like this
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>F</th><th>R</th><th>S</th><th>T</th><th>U</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">176</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;border-bottom: 1px solid black;background-color: #92D050;;">182</td><td style="text-align: right;background-color: #00FF00;;">194</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">170</td><td style="text-align: right;background-color: #00FF00;;">170</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">161.2308</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">157</td><td style="text-align: right;background-color: #00FF00;;">171</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">161.2308</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">149</td><td style="text-align: right;background-color: #00FF00;;">145</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">205</td><td style="text-align: right;background-color: #00FF00;;">134</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">180</td><td style="text-align: right;background-color: #00FF00;;">167</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">164</td><td style="text-align: right;background-color: #00FF00;;">208</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">157</td><td style="text-align: right;background-color: #00FF00;;">144</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">157</td><td style="text-align: right;background-color: #00FF00;;">146</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">180</td><td style="text-align: right;background-color: #00FF00;;">154</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">165</td><td style="text-align: right;background-color: #00FF00;;">166</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">183</td><td style="text-align: right;background-color: #00FF00;;">183</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">151</td><td style="text-align: right;background-color: #00FF00;;">201</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">146</td><td style="text-align: right;background-color: #00FF00;;">124</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">140</td><td style="text-align: right;background-color: #00FF00;;">196</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">146</td><td style="text-align: right;background-color: #00FF00;;">175</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">144</td><td style="text-align: right;background-color: #00FF00;;">175</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">144</td><td style="text-align: right;background-color: #00FF00;;">199</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">244</td><td style="text-align: right;background-color: #00FF00;;">193</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">205</td><td style="text-align: right;background-color: #00FF00;;">206</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">140</td><td style="text-align: right;background-color: #00FF00;;">148</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">180</td><td style="text-align: right;background-color: #00FF00;;">187</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #92D050;;">173</td><td style="text-align: right;background-color: #00FF00;;">195</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">U11</th><td style="text-align:left">=AVERAGEIF(<font color="Blue">R10:R209,"<"&U8,F10:F209</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">U12</th><td style="text-align:left">{=AVERAGE(<font color="Blue">IF(<font color="Red">R10:R32<U8,F10:F32</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />