Average using multiple conditions

[tgriffin]

I would like to calculate the average Evaluation Response IF the following conditions are met:

1. The Evaluation Title = 'Instructional Evaluation'

2. The Prior Fiscal Year= the user selection

Current formula is: 

avg({<EvaluationTitle={'Instructional Evaluation'}>} {<PriorFY>} response_value)

The issue is that the calculated average is higher than it should be. It calculates avg 4.91, when it should be 4.82

If I move the {<PriorFY>} to the beginning of the statement, the calculated average is even higher (4.97).

avg({<PriorFY>}{<EvaluationTitle={'Instructional Evaluation'}>} response_value)

I must be missing brackets, parentheses, or something...

What can you suggest?

Thanks,
Troy

[neelamsaroha157 · Accepted solution]

I think the syntax should be something like  - Avg({<YearField = {$(PriorFY)}, EvaluationTitle={'Instructional Evaluation'}>}Response_Value)

assuming that you have PriorFY stored in a variable.

[tgriffin · Accepted solution]

Many thanks Neelamsaroha157

-- with a little tweaking, I was able to get the correct results.

Here is the final working formula:

Avg({<FY=PriorFY, EvaluationTitle={'Instructional Evaluation'}>}response_value)