o Summarize the information by application type (Regular, Transfer). Write a formula in cell C15 that will determine the number of students that are transfer students. Write the formula so that it may be copied down the column to determine the number of Regular applicants. Write a formula in cell 015 to calculate the average Verbal scores for each type (T/R) of student application. Write the formula so that it may be copied down the column and across the row into cells D15:F16 to calculate the average for regular student's Verbal SAT and then for each student category Math SAT etc. . G H 1 B E Evaluation of Candidates 20% 40% 40% 2 Weight in final score: Verbal SAT SCI SAT Weighted II score Math SAT 3 Name Type 4 Mary Keck R 680 670 580 '636 5 Alex Jones 770 690 640 686 T 700 730 670 700 IR 590 780 660 694 6 Mark Wale 7 Timoty Watkins 8 Scott Sharp 9 Brent Smith T 690 800 710 742 R 780 720 730 736 10 AVERAGE 701.67 731.67 665.00 699.00 11 MINIMUM 590.00 580.00 636.00 670.00 800.00 12 MAXIMUM 780.00 730.00 742.00 13 #of students AVG (verbal) AVG (math) AVG (SCI) 14 15 transfer students T 16 regular students R