formulas

[S. Balcom]

I have a question regarding creating a formula. I need to create an average formula in one field D. It needs to accommodate the following conditions:

If there is a score in field A, field B and Field C then divide the sum of these three by 3

If there is a score in field A and field B then divide the sum of these by 2

If there is a score in Field B and C then divide the sum of these by 2

If there is a score in Field A and C then divide the sum of these by 2

If there is a score in field A then divide by 1

If there is a score in Field B then divide by 1

If there is a score in Field C then divide by 1

Thank you to anyone out there who can help me with this problem.

[PinnWal]

Sounds like another job for a Case Statement :-)

This should accomplish what you're trying to do...

Case(

not IsEmpty(Test A) and not IsEmpty(Test : and not IsEmpty(Test C), (Test A + Test B + Test C) / 3,

not IsEmpty(Test A) and not IsEmpty(Test and IsEmpty(Test C), (Test A + Test / 2,

not IsEmpty(Test A) and IsEmpty(Test and not IsEmpty(Test C), (Test A + Test C) / 2,

IsEmpty(Test A) and not IsEmpty(Test and not IsEmpty(Test C), (Test B + Test C) / 2,

not IsEmpty(Test A) and IsEmpty(Test and IsEmpty(Test C), Test A / 1,

IsEmpty(Test A) and not IsEmpty(Test and IsEmpty(Test C), Test B / 1,

IsEmpty(Test A) and IsEmpty(Test and not IsEmpty(Test C), Test C / 1, 0)

Hope that's helpful...

Later,

Paul :-)

[danjacoby]

Or you can create a calc field (call it "Fields"):

If(isEmpty (A), 1, 0) + If(isEmpty (, 1, 0) + If(isEmpty ©, 1, 0).

Then your final field =

(A+B+C)/Fields

HTH,

Dan

[danjacoby]

Wups!

That "Fields" calc should be:

If(isEmpty (A), 0, 1) + If(isEmpty (:, 0, 1) + If(isEmpty ©, 0, 1)

Whew!