cancel
Showing results for 
Search instead for 
Did you mean: 
ShellBelle80
Nintex Newbie

Please Help Average Formula Support

Hoping somone can help, I feel like this should be so simple......

I have a column I want to average, there are 3 scores possible in the column:

100 

0

N/A

 

The N/A is null so if this is selected it should be removed from the average, the " 0 " should be calculated in the average when selected.

 

Currently I have the N/A being counted as null, however, "0" is not being considered.  

 

I want to be able to include the " 0 " in the average and keep the N/A as null when selected.

 

The current formula I am using (which appeared to be working Friday and no longer worked as of yesterday)

 

avg(If(01,02,03,04,05,06,07,8, '<>N/A' ))

 

Deadline has been missed due to this, so any help is GREATLY appreciated!!!


Thanks so much!

 

Michelle

Labels: (2)
0 Kudos
Reply
1 Reply
bamaeric
Nintex Newbie

Re: Please Help Average Formula Support

One way to accomplish this is with a site workflow.  I am assuming a couple of things: the Score column is a choice field with 3 choices (100, 0, N/A) and each item in the list has only one score.  Here are the steps:

 

  1. Create a site workflow.
  2. Add a "Query List" action.  Configure it with a Filter to select all items when Score not equals "N/A" and store the ID fields in a Collection workflow variable (for example, colScores).
  3. Add a "Collection Operation" action.  Configure it with the Target Collection from Step 2, select Count option and "Store Result In" a Number workflow variable (for example, numScoresCount).
  4. Add a "For Each" action to loop through the colScores collection and store the result in a Text workflow variable (for example, txtID).
  5. Add a "Set Variable" action within the "For Each" action.  Configure that action to set a Number workflow variable (for example, numSingleScore) to look up the Score value for each item in the collection and store the choice (100 or 0).
  6. Add a "Math Operation" within the "For Each" action to add the numSingleScore variable to a total sum number workflow variable (for example, numScoresSum) and store the result in the numScoresSum variable.
  7. Add a "Math Operation" outside the "For Each" action.  Configure it as numScoresSum divided by numScoresCount and store result (average) in a Number workflow variable (for example, numScoresAvg).
  8. That gives you the average score of all Scores in a list that are not "N/A".

Below is an image of the workflow design:

 

ColumnAverageWorkflow.png

 

 

 

 

0 Kudos
Reply