MichaelMartin1
Qrew Trainee
3 years ago

# Formulated Calculations

Hello,

I am trying to build an "assessment" application that takes different grades and calculates overall scores. The issue I am currently having is that lets say I have three numeric fields that are 1-10, and a score field that takes the aforementioned three fields and sums them and then averages. How would I go about creating a calculation where if one of the grades were not applicable, it would average the other two without needing the N/A field to complete the calculation?

------------------------------
Michael Martin
------------------------------

### 5 Replies

• Try this
Average(
IF([Field 1]<>"N/A", [Field 1 score]),
IF([Field 2]<>"N/A", [Field 2 score]),
IF([Field 3]<>"N/A", [Field 3 score]))

That will only average the non null values, so if field 1  = "N/A" it would be omitted from the Average.

if you also need the sum

IF([Field 1]<>"N/A", [Field 1 score],0)
+
IF([Field 2]<>"N/A", [Field 2 score],0)

IF([Field 3]<>"N/A", [Field 3 score],0)

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
• Hey Mark, I'm getting the error that I can not use the <> operator with numbers or text.

Also, I am using one Field for each graded question, so I am not sure how to manipulate what I am trying to do with your solution.

For example, I will pretend I have the three questions:

Normally, I'd take the three grades and average them for the overall 'cleanliness' score. But if the next location I go to does not have windows, I would need to omit that from the overall average of the 'cleanliness' score.

------------------------------
Michael Martin
------------------------------
• I was now able to get it to work using your original code with a little bit of work.

var number blank = ToNumber("N/A");

Average(
If([Signage Lit / Easily Viewable Day & Night]<>\$blank,[Signage Lit / Easily Viewable Day & Night]),