Hi! I have an issue/challenge I’m trying to solve with Monday Formulas that I don’t think is currently possible and would love to see the feature in Monday.com at some point.
Context here is I’m trying to average many rating columns from a survey done through Monday Workdocs. Which would seem simple enough, but has been quite troublesome.
Every submission to the survey creates an item. Some questions are conditional and not every person who takes the survey gets every question. For example, different teams get different sets of questions. This leaves some questions unanswered. When trying to average all of the rating columns, if one of the questions is unanswered, its default value is 0, this is a problem because it skews the average when added to a formula. I want to only average any column that is not equal to zero. The only formula I could come up with for this is:
This formula will work, however it works because Monday cannot take the average of “N/A” and when blank instead of outputting “N/A” to the formula columns, it’s just an error.
If this were excel I could use the formula: AVERAGEIF(range, criteria) and it would average anything that isn’t 0, if I set “<>0” in the criteria. It would also be a lot easier to make, because the range would just be a list of every column. I was able to get this excel code working in Monday using the General Caster app, however I quickly hit the free operations cap and this survey doesn’t outweigh the cost of General Caster.
I would love if Monday had a formula or feature that could solve this issue of not wanting to average blank/null/0 values. Perhaps there is a very simple formula or phrase that will solve this problem but I have scoured the formula documentation and haven’t been successful.
If anyone has any feedback or suggestions it would be very helpful.