Advanced Filter

rgnsuphc · Feb 25, 2011

I am trying to filter the following

 1. All students who scored 90 or more on exams 1,2,3
2. All students who scored 65 or less on all three exams
3. All students who scored at least 80 on the paper AND 160 on the final exam

This is my data base:

1 ( STARTS WITH A2) Grades (L2-P2)

<table border="0" cellpadding="0" cellspacing="0" width="1080"><col style="width: 54pt;" span="15" width="72"> <tbody><tr style="height: 15.75pt;" height="21"> <td class="xl67" style="height: 15.75pt; width: 54pt;" height="21" width="72">Student</td> <td class="xl67" style="width: 54pt;" width="72">Exam1</td> <td class="xl67" style="width: 54pt;" width="72">Exam2</td> <td class="xl67" style="width: 54pt;" width="72">Exam3</td> <td class="xl67" style="width: 54pt;" width="72">Paper</td> <td class="xl67" style="width: 54pt;" width="72">Final</td> <td class="xl67" style="width: 54pt;" width="72">Total</td> <td class="xl67" style="width: 54pt;" width="72">Percent</td> <td class="xl67" style="width: 54pt;" width="72">Grade</td> <td style="width: 54pt;" width="72">
</td> <td style="width: 54pt;" width="72">
</td> <td style="width: 54pt;" width="72">
</td> <td style="width: 54pt;" width="72">
</td> <td style="width: 54pt;" width="72">
</td> <td style="width: 54pt;" width="72">
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">1</td> <td align="right">92</td> <td align="right">90</td> <td align="right">89</td> <td align="right">100</td> <td align="right">175</td> <td align="right">546</td> <td class="xl69" align="right">0.91</td> <td class="xl70">A</td> <td>
</td> <td>
</td> <td class="xl70">0</td> <td class="xl70">0.6</td> <td class="xl70">0.64</td> <td class="xl70">0.8 .9
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">2</td> <td align="right">99</td> <td align="right">100</td> <td align="right">98</td> <td align="right">100</td> <td align="right">185</td> <td align="right">582</td> <td class="xl69" align="right">0.97</td> <td class="xl70">A</td> <td>
</td> <td>
</td> <td class="xl70">F</td> <td class="xl70">D</td> <td class="xl70">C</td> <td class="xl70">B A
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">3</td> <td align="right">60</td> <td align="right">55</td> <td align="right">90</td> <td align="right">100</td> <td align="right">134</td> <td align="right">439</td> <td class="xl69" align="right">0.73</td> <td class="xl70">C</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">4</td> <td align="right">55</td> <td align="right">75</td> <td align="right">75</td> <td align="right">85</td> <td align="right">100</td> <td align="right">390</td> <td class="xl69" align="right">0.65</td> <td class="xl70">C</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">5</td> <td align="right">85</td> <td align="right">85</td> <td align="right">87</td> <td align="right">87</td> <td align="right">190</td> <td align="right">534</td> <td class="xl69" align="right">0.89</td> <td class="xl70">B</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">6</td> <td align="right">65</td> <td align="right">72</td> <td align="right">78</td> <td align="right">80</td> <td align="right">125</td> <td align="right">420</td> <td class="xl69" align="right">0.70</td> <td class="xl70">C</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">7</td> <td align="right">93</td> <td align="right">90</td> <td align="right">98</td> <td align="right">85</td> <td align="right">187</td> <td align="right">553</td> <td class="xl69" align="right">0.92</td> <td class="xl70">A</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">8</td> <td align="right">95</td> <td align="right">97</td> <td align="right">97</td> <td align="right">84</td> <td align="right">191</td> <td align="right">564</td> <td class="xl69" align="right">0.94</td> <td class="xl70">A</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">9</td> <td align="right">88</td> <td align="right">75</td> <td align="right">80</td> <td align="right">80</td> <td align="right">160</td> <td align="right">483</td> <td class="xl69" align="right">0.81</td> <td class="xl70">B</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">10</td> <td align="right">91</td> <td align="right">80</td> <td align="right">99</td> <td align="right">100</td> <td align="right">172</td> <td align="right">542</td> <td class="xl69" align="right">0.90</td> <td class="xl70">A</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">11</td> <td align="right">88</td> <td align="right">78</td> <td align="right">63</td> <td align="right">80</td> <td align="right">135</td> <td align="right">444</td> <td class="xl69" align="right">0.74</td> <td class="xl70">C</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">12</td> <td align="right">78</td> <td align="right">99</td> <td align="right">95</td> <td align="right">85</td> <td align="right">154</td> <td align="right">511</td> <td class="xl69" align="right">0.85</td> <td class="xl70">B</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">13</td> <td align="right">90</td> <td align="right">92</td> <td align="right">65</td> <td align="right">90</td> <td align="right">50</td> <td align="right">387</td> <td class="xl69" align="right">0.65</td> <td class="xl70">C</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">14</td> <td align="right">82</td> <td align="right">99</td> <td align="right">97</td> <td align="right">80</td> <td align="right">150</td> <td align="right">508</td> <td class="xl69" align="right">0.85</td> <td class="xl70">B</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">15</td> <td align="right">95</td> <td align="right">94</td> <td align="right">92</td> <td align="right">85</td> <td align="right">150</td> <td align="right">516</td> <td class="xl69" align="right">0.86</td> <td class="xl70">B</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">16</td> <td align="right">91</td> <td align="right">87</td> <td align="right">80</td> <td align="right">80</td> <td align="right">42</td> <td align="right">380</td> <td class="xl69" align="right">0.63</td> <td class="xl70">D</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">17</td> <td align="right">86</td> <td align="right">73</td> <td align="right">70</td> <td align="right">70</td> <td align="right">155</td> <td align="right">454</td> <td class="xl69" align="right">0.76</td> <td class="xl70">C</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">18</td> <td align="right">45</td> <td align="right">50</td> <td align="right">54</td> <td align="right">65</td> <td align="right">100</td> <td align="right">314</td> <td class="xl69" align="right">0.52</td> <td class="xl70">F</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">19</td> <td align="right">35</td> <td align="right">80</td> <td align="right">52</td> <td align="right">45</td> <td align="right">75</td> <td align="right">287</td> <td class="xl69" align="right">0.48</td> <td class="xl70">F</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">20</td> <td align="right">88</td> <td align="right">72</td> <td align="right">76</td> <td align="right">72</td> <td align="right">141</td> <td align="right">449</td> <td class="xl69" align="right">0.75</td> <td class="xl70">C</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">21</td> <td align="right">25</td> <td align="right">75</td> <td align="right">90</td> <td align="right">90</td> <td align="right">100</td> <td align="right">380</td> <td class="xl69" align="right">0.63</td> <td class="xl70">D</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">22</td> <td align="right">66</td> <td align="right">85</td> <td align="right">88</td> <td align="right">80</td> <td align="right">178</td> <td align="right">497</td> <td class="xl69" align="right">0.83</td> <td class="xl70">B</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">23</td> <td align="right">54</td> <td align="right">70</td> <td align="right">50</td> <td align="right">60</td> <td align="right">125</td> <td align="right">359</td> <td class="xl69" align="right">0.60</td> <td class="xl70">F</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">24</td> <td align="right">88</td> <td align="right">86</td> <td align="right">89</td> <td align="right">90</td> <td align="right">167</td> <td align="right">520</td> <td class="xl69" align="right">0.87</td> <td class="xl70">B</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">25</td> <td align="right">85</td> <td align="right">83</td> <td align="right">89</td> <td align="right">95</td> <td align="right">200</td> <td align="right">552</td> <td class="xl69" align="right">0.92</td> <td class="xl70">A</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">26</td> <td align="right">78</td> <td align="right">85</td> <td align="right">70</td> <td align="right">70</td> <td align="right">165</td> <td align="right">468</td> <td class="xl69" align="right">0.78</td> <td class="xl70">C</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">27</td> <td align="right">99</td> <td align="right">93</td> <td align="right">97</td> <td align="right">90</td> <td align="right">188</td> <td align="right">567</td> <td class="xl69" align="right">0.95</td> <td class="xl70">A</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">28</td> <td align="right">67</td> <td align="right">89</td> <td align="right">70</td> <td align="right">78</td> <td align="right">150</td> <td align="right">454</td> <td class="xl69" align="right">0.76</td> <td class="xl70">C</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">29</td> <td align="right">88</td> <td align="right">90</td> <td align="right">97</td> <td align="right">90</td> <td align="right">130</td> <td align="right">495</td> <td class="xl69" align="right">0.83</td> <td class="xl70">B</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">30</td> <td align="right">67</td> <td align="right">89</td> <td align="right">90</td> <td align="right">100</td> <td align="right">139</td> <td align="right">485</td> <td class="xl69" align="right">0.81</td> <td class="xl70">B</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>

I attempted so far: List Range - $B$2:$D$31
Criteria Range- $R$1:$R$2
Copy to: $S$2

Criteria range (R): Exam Copy to (S): Student

>=90 92( I do not know why I get

this number instead of getting how many students that got a 90 or better on all 3 exams. )

cjcobra · Feb 25, 2011

I have this pulling the student number.

But this will show what you need to see.

1.
=IF(AND(B7>=90,C7>=90,D7>=90),A7,"")

2.
=IF(AND(B7<=65,C7<=65,D7<=65),A7,"")

3.
=IF(AND(E7>=80,F7>=160),A7,"")

rgnsuphc · Feb 26, 2011

I need to use the advanced filter. I am not really understanding what I am doing wrong. I have been trying to do what exactly this link is stating:http://www.excelfunctions.net/ExcelAdvancedFilter.html

If someone could just give me very specific steps on what to do.

Advanced Filter

rgnsuphc

Board Regular

Excel Facts

cjcobra

Board Regular

rgnsuphc

Board Regular

Similar threads

Forum statistics

Share this page

Advanced Filter

rgnsuphc

Board Regular

Excel Facts

cjcobra

Board Regular

rgnsuphc

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock