Results 1 to 2 of 2
Thread: nested if

120704, 22:04 #1Registered User
 Join Date
 Dec 2004
 Posts
 1
Unanswered: nested if
I'm trying to set upTerm 1 for my school.
Projects is worth 30%, Homework is worth 30%, Attitude is worth 10%, and Exams are worth 30%........I'd like to keep a running average. The first 4 lnes here below work fine, but (5th line) when I try to calculate the average when 2 of the 4 fields are empy, it won't work......Please take a look at the "mess below" (from the 5th line onward) and see where I went wrong.
Thank you for you time and expertise
Drew
If( IsEmpty(Projects 1), Sum(Homework 1,Attitude 1,Exams 1)/.70,
If( IsEmpty(Homework 1), Sum(Projects 1,Attitude 1,Exams 1)/.70,
If( IsEmpty(Attitude 1), Sum(Projects 1,Homework 1,Exams 1)/.90,
If( IsEmpty(Exams 1), Sum(Projects 1,Homework 1,Attitude 1)/.70,
If( IsEmpty( Projects 1) & IsEmpty(Homework 1), Sum(Attitude 1,Exams 1)/.40,
If( IsEmpty(Projects 1) & IsEmpty(Attitude 1), Sum(Homework 1,Exams 1)/.60,
If( IsEmpty(Projects 1) & IsEmpty( Exams 1) , Sum(Homework 1,Attitude 1)/.40,
If( IsEmpty( Homework 1) & IsEmpty( Attitude 1), Sum(Projects 1,Exams 1)/.60,
If( IsEmpty( Homework 1) & IsEmpty(Exams 1), Sum(Projects 1,Attitude 1)/.40,
If( IsEmpty(Attitude 1) & IsEmpty(Exams 1), Sum(Projects 1,Homework 1)/.60,
If( IsEmpty(Projects 1) & IsEmpty(Homework 1) & IsEmpty( Attitude 1), Sum(Exams 1)/.30,
If( IsEmpty(Homework 1) & IsEmpty(Attitude 1) & IsEmpty(Exams 1), Sum(Projects 1)/.30,
If( IsEmpty(Attitude 1) & IsEmpty(Exams 1) & IsEmpty(Projects 1) , Sum(Homework 1)/.30,
If( IsEmpty( Exams 1) & IsEmpty(Projects 1) & IsEmpty(Homework 1), Sum(Attitude 1)/.10,
Sum( Projects 1,Homework 1,Attitude 1,Exams 1)))))))))))))))

122104, 07:10 #2Registered User
 Join Date
 May 2004
 Location
 Melbourne, Australia
 Posts
 23
Hi,
Two things. Firstly, you woudl be better off using the Case( ) function for this, as it is specifically designed to haldle a series of tests and results within a single function call (no need for nesting).
Secondly, you should bear in mind that the expressions will be evaluated stricly in the order that they are presented, so the first result that is valid will be returned.