Discussions

 View Only

  • 1.  Formulated Calculations

    Posted 9 days ago
    Edited by Michael Martin 9 days ago
    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
    ------------------------------


  • 2.  RE: Formulated Calculations

    Posted 9 days ago
    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
    ------------------------------

    Original Message


  • 3.  RE: Formulated Calculations

    Posted 8 days ago

    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
    ------------------------------

    Original Message


  • 4.  RE: Formulated Calculations

    Posted 8 days ago
    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
    ------------------------------

    Original Message


  • 5.  RE: Formulated Calculations

    Posted 8 days ago
    What is the fields that tell you if you have windows?

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------

    Original Message


  • 6.  RE: Formulated Calculations

    Posted 8 days ago
    Edited by Michael Martin 8 days ago

    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
    ------------------------------

    Original Message