Expression Error

[donnchaoc]

Trying to get this expression to work to add up the results of 3 Exams and find the average but I want it to take into account if one of the exams has a value of Null and only divide by 2 then for the average. It keeps telling me I have the wrounf number of arguements :



IIf([MCQ] = Null,(Int([Project])+Int([Exam]))/2, (Int([Project])+Int([Exam])+Int([MCQ]))/3,

IIf( [Exam] = Null, (Int([Project])+Int([MCQ]))/2, (Int([Project])+Int([Exam])+Int([MCQ]))/3,

IIf( [Project] = Null,(Int([MCQ])+Int([Exam]))/2, (Int([Project])+Int([Exam])+Int([MCQ]))/3)))



Any help would be greatly appreciated.

[pbaldy]

For starters, try the IsNull() function; nothing is "= Null". Secondly, the IIf() function has 3 arguments; you're trying to stuff 4 into it. For example, the first line has all 3 arguments, but then you add a comma and try to add more.

[donnchaoc]

I need all the 3 lines within the one in statement and that is why i have the comma at the end of each line.

[rpeare]

what happens if 2 or more of them are null? you haven't accounted for that other than relying on a waterfall effect that will fail if there are instance where more than one is null.

You have 8 possible outcomes

MCQ	Exam	Project	Formula Part
null	null	null	1
null	null	Something	1
null	something	null	1
something	null	null	2
null	something	something	1
something	null	something	2
something	something	null	3
something	something	something	3

If you make the change to use isnull() as suggested by pbaldy this is how your formula is going to decide what goes where which I don't think is your intended result because if you have 2 or more fields with a null value your formula will return nothing. Note that if you were to then change it so the EXAM field was first in your IIF statement you would get radically different results.

You've got to figure out how you want to handle each combination of your results and build your formula around that.

[donnchaoc]

Ok so I have it working now as far as if I have a null field in either of the 3 fields:

IIf(Int([MCQ]) Is Null,(Int([Project])+Int([Exam]))/2,IIf(Int([Exam]) Is Null,(Int([MCQ])+Int([Project]))/2,IIf(Int([Project]) Is Null,(Int([MCQ])+Int([Exam]))/2,(Int([MCQ])+Int([Project])+Int([Exam]))/3)))


But if i add in :
"IIf(Int([Project]) Is Null AND (Int([MCQ]) Is Null, (Int([Exam]))/1, " before "(Int([MCQ])+Int([Project])+Int([Exam]))/3))" at the end it throws an error

[pbaldy]

Does this shot-in-the-dark work?

(Int(Nz([Project],0))+Int(Nz([Exam],0))+Int(Nz([MCQ],0)))/(3 + IsNull([Project])+IsNull([Exam])+IsNull([MCQ]))

By the way, 3 fields that may or may not be Null is indicative of a normalization issue.

[rpeare]

I would do this:

IIf(IIf(IsNull([mcq]),0,1)+IIf(IsNull([project]),0,1)+IIf(IsNull([exam]),0,1)<2,Null,(Nz([mcq],0)+Nz([project],0)+Nz([exam],0))/(IIf(IsNull([mcq]),0,1)+IIf(IsNull([project]),0,1)+IIf(IsNull([exam]),0,1)))

in other words if you don't have at least 2 results you don't want a result at all that's the indication of your original formula, that at least 2 scores must be present.

the formula above would also work for 1 score if you changed it to

IIf(IIf(IsNull([mcq]),0,1)+IIf(IsNull([project]),0,1)+IIf(IsNull([exam]),0,1)<1,Null,(Nz([mcq],0)+Nz([project],0)+Nz([exam],0))/(IIf(IsNull([mcq]),0,1)+IIf(IsNull([project]),0,1)+IIf(IsNull([exam]),0,1)))