Forms: Setting up proper formula for "When non-mandatory fields are empty, exclude from average calculation"

[TFaust (Timo Faust)]

Hello everyone,

I rather enjoy being on the answering side of these forums, but today I come with another request for input from this great community.

Background to my question:
I am setting up a questionnaire, using Monday forms, to help me evaluate the soft skills of project managers after a project has been completed, and it needs to be filled out anonymously by the project teams (client / our company).

The questionnaire will have different parts, one of which will be “Communication skills”, which will be divided into 5 questions, which can be rated from 1 to 5. All fields are optional.
Later on I want to weight the answers, but that is not relevant for my question as I have workarounds in mind.

Here is a screenshot of the incoming answers to my questionnaire (still early prototype):

Marked in green are the incoming responses.
Marked in purple are the averages of the answers. Note that “active listening” was not answered by one of the participants, resulting in a zero star rating.
Cleverly, Monday recognises that there was no answer and excludes it from the averages, correctly resulting in an average of 4.0 stars instead of 2.667 which would be the result of a typical Monday “averages” formula.
Marked in blue is my overall score for all communication skills. If I want to have an overall average for “communication skills”, I use the formula

(SUM({verbal communication},{email communication},{active listening},{empathy},{status reports}))

Unfortunately, this does not take into account the zero star rating, and it messes up my overall analysis.
The correct result would be 20.3/25 stars, while the formula downgrades it to 19/25 stars.

My question now is:

How can I set up a formula that uses the values excluding “zero star ratings”, like the Monday column summary does?

Thanks in advance, I hope I have made my request clear as English is not my first language and sometimes I cannot express my thoughts as eloquently as I would like.

Have a great day!
Timo

EDIT: For those interested: My approach for weighted results would look like this:

CHOOSE({verbal communication}+1,0,1,2,3,4,5) +
CHOOSE({email communication}+1,0,1,2,3,4,5) +
CHOOSE({active listening}+1,0,0.5,1,1.5,2,2.5) +
CHOOSE({empathy}+1,0,0.5,1,1.5,2,2.5) +
CHOOSE({status reports}+1,0,1,2,3,4,5)

Sadly, again, I have no idea how to exclude the result “0” from what I am planning to do, setting it to “n/a” just gives me a total of n/a, so I clearly need a way to ignore values of zero.

[GCavin (Gilles Cavin - Reinventing Formulas in monday.com)]

Hi Timo,

Try:

AVERAGE(IF({verbal communication}=0,"",{verbal communication}),IF({email communication}=0,"",{email communication}),IF({active listening}=0,"",{active listening}),IF({empathy}=0,"",{empathy}),IF({status reports}=0,"",{status reports}))

 
 

---

Curious to know how that same formula would look in the Advanced Formula Booster, the 3rd-party app that makes it so easy to write formulas?

[TFaust (Timo Faust)]

Hi Gilles,

thank you very much for your reply, as it has been super helpful. I was basically one step away from the solution myself and you saved me some serious headache, and more importantly, time

Now on to implementing the weighting system…

I wish you a great week! Thanks a lot!

Timo

[TFaust (Timo Faust)]

In order to share some knowledge, here are some of the formulas I used to set up my evaluation sheets, may it help somebody else in the future

(SUM(
IF({Freundlichkeit und Empathie}=0,“”,(CHOOSE({Freundlichkeit und Empathie}+1,0,0.5,1,1.5,2,2.5))),
IF({Verlässlichkeit und Integrität}=0,“”,(CHOOSE({Verlässlichkeit und Integrität}+1,0,1,2,3,4,5))),
IF({Zeitmanagement}=0,“”,(CHOOSE({Zeitmanagement}+1,0,1,2,3,4,5))),
IF({Zielorientierung und Ergebnisorientierung}=0,“”,(CHOOSE({Zielorientierung und Ergebnisorientierung}+1,0,1,2,3,4,5))),
IF({Analytisch und objektiv}=0,“”,(CHOOSE({Analytisch und objektiv}+1,0,1,2,3,4,5))))
/(4.5-(
(IF({Freundlichkeit und Empathie}=0,0.5,0))+
(IF({Verlässlichkeit und Integrität}=0,1,0))+
(IF({Zeitmanagement}=0,1,0))+
(IF({Zielorientierung und Ergebnisorientierung}=0,1,0))+
(IF({Analytisch und objektiv}=0,1,0))
)))/5*100

This formula adds up all the answers from the “professionalism” block, weighs them and then gives me the result as a percentage.

((3*{“Kommunikation”})+
(2*{“Professionalität”})+
(2*{“Führungsqualitäten”})+
(3*{“Planung und Organisation”}))/10

This formula then sums up the results of the 4 evaluation blocks and weighs them for a total result.

In the end I set up a mirror board, as we will use evaluations from customers and evaluations from superiors across a few projects so we can use that data also in performance reviews.

Is this all elegant? No. Does it work? Yes. Good enough for me

You all have a nice week!