Need help creating AVERAGEIF style function for a survey using rating columns

[Bernd01 (Bernd Hoffmann)]

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:

Average(IF({Question1}>0,{Question1},“N/A”),IF({Question2}>0,{Question2},“N/A”),IF{Question3}>0,{Question},“N/A”))

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.
Thanks!

[Nachliel (Nachliel from Easyapps)]

Hey

Nachliel from Easyapps here

What worked for me was a slightly simpler version of the same formula, you’ll get the same results but it is easier to write and read.
AVERAGE(IF({Rating 1},{Rating 1}),IF({Rating 2},{Rating 2}))

Regardless, if you are interested in creating styled forms that better match your brand and embed perfectly in you web page you can check out our monday.com app Easyform and our extensive documentation site.

You can set up a free demo here or contact us at support@easyappshq.com.

Here is an example of our form in action:

I hope this helps

Nachliel

[Bernd01 (Bernd Hoffmann)]

Hi Nachliel, thanks for the response.

I suppose that is helpful in that I was able to reproduce my own results with a better looking formula, however it’s not helpful in that I don’t think I understand why it works.

Doing some research it appears that IF statements will work without the “Else”. This is great, however the formula IF(0,0) and IF(0), just return “True”, so it seems the only reason that our formulas are working with AVERAGE() is because the formulas can’t average “true/false” but they can average numbers… So the formula:

AVERAGE( IF({Rating 1},{Rating 1}),IF({Rating 2},{Rating 2},IF(0,0),IF(0) ) )

…Also returns the same results. Yet the code: IF(0,0,0) will skew the average, because it outputs “0”.
I clearly don’t have a perfect grasp on the IF(logic,true,false), I feel like I’m playing Calvin Ball with Monday formulas over here since the documentation is so limited, and I’m only getting the results I want out of the formula when it’s broken.

I also recognize Monday community is one giant opportunity to advertise your own apps, and I respect the hustle.

This all isn’t to say I don’t appreciate your response, it was actually very helpful in that is taught me why Monday’s AVERAGE() formula has been giving me the results it has been.

I just hope one day monday will provide AVERAGEIF() to prevent this type of headache in the future.