yogi_Compute Stats in A Computed Column Based On Entries in Multiple Columns

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #716   Aug 28, 2012     www.energyefficientbuild.com.

user B-House said: (http://productforums.google.com/forum/?zx=4uvknqql63q3#!category-topic/docs/spreadsheets/jBW6-R8PRXI)
Array Formula with If forumla 
I am trying to create a formula that will determine a student's Tier (1,2,3) based on 5 criteria.

I have 5 columns(F:J) that serve as the Criteria for K

• F is the number of D grades
• G is the number of F grades
• H is the number of unexcused absences
• I is the number of tardies
• J is the number of behavior referrals

Column K is the Tier Level (1, 2, 3)

• I would like column K to fill with a 1 if:
• F:J = 0
• I would like column K to fill with a 2 if:
• G+H+J = 0
• AND I < 3
• I would like column K to fill with a 3 If:
• G+H+J > 1
• OR I >3

Does this make sense? I shared a link to my form just in case. 

https://docs.google.com/spreadsheet/ccc?key=0Agi0HENAQ7xLdEdMSFVvLTRQWkd4X2ppS0VQMk9Ia3c

Thanks in advance! You all do great work and I truly appreciate the help.

Carl

---------------------------------------------------------------------------------------------

following is a solution to the problem

yogi_Compute GPA For Number Of Tests Taken Ignoring N/A For Tests Not Taken

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #695   Aug 14, 2012     www.energyefficientbuild.com.

user B-House said: (http://productforums.google.com/forum/?zx=76albpq7qu02#!category-topic/docs/spreadsheets/_2mcpQHtUOU%5B1-25%5D)
Excluding Text when using ArrayFormula for Average in Spreadsheet
I am trying to create a google form to track a student's GPA. We have 4 classes and I used this formula 

=ArrayFormula( (D2:D+E2:E+F2:F+G2:G) / 4 )The problem that I have is that some students do not take 4 courses. I used N/A is an option on the google form for this instance. The ArrayFormula will not exclude N/A when calculating the average. I need a formula to ignore the N/A and divide by the appropriate number of numerical responses in these circumstances. I hope that this makes sense. I included the link and it should be editable. I appreciate the help!

https://docs.google.com/spreadsheet/ccc?key=0Agi0HENAQ7xLdDFrY0hLeXpIRHpYT0hid2NEcVpkZFE

------------------------------------------------------------------------------------

following is a solution to the problem