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 ...
Forum Discussion
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
------------------------------
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:
Wall Cleanliness: (1-10 numeric grade)
Floor Cleanliness: (1-10 numeric grade)
Window Cleanliness: (1-10 numeric grade)
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]),
If([Reader Board Working]<>$blank,[Reader Board Working]),
If([Entrance / Exit Obvious and Easily Understood]<>$blank,[Entrance / Exit Obvious and Easily Understood]))
Thank you Mark!
------------------------------
Michael Martin
------------------------------ - What is the fields that tell you if you have windows?
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------A consultant will be physically on site to conduct the assessment, so if he arrives and the location has no windows, he'd select "N/A" for the grade of that field. Not sure if you saw my reply to myself, but I was able to use your code and make it work.
This is an example of what I did for one category:
var number blank = ToNumber("N/A");Average(
If([Signage Lit / Easily Viewable Day & Night]<>$blank,[Signage Lit / Easily Viewable Day & Night]),
If([Reader Board Working]<>$blank,[Reader Board Working]),
If([Entrance / Exit Obvious and Easily Understood]<>$blank,[Entrance / Exit Obvious and Easily Understood]))
Thank you for your help!
------------------------------
Michael Martin
------------------------------
