Average Collect formula

Amy Swan

I'm trying to collect the average survey response number for a quarter for the current year and multiply it by 0.2, for a percentage, but it's coming back unparseable.

The first referenced column is a 1-5 number, the months are from a numerical month helper column, 'Created' is the auto-create date. Any ideas?

=AVERAGE(COLLECT({My provider treated me with respect}, {Month}, 4, {Month}, 5, {Month}, 6, YEAR({Created}), YEAR(TODAY()))) * 0.2

Philip Robbins

I expect it's the YEAR({Created}) part that's causing the error, but I do wonder if the way you're handling months also needs fixing, as no row is going to be all three months at once.

I would change it to:

=AVG(COLLECT({My provider treated me with respect}, {Month}, OR(@cell=4, @cell=5, @cell=6), {Created}, YEAR(@cell)=YEAR(TODAY()))) * 0.2

Note you might want a helper column for the year of your Created column, at which point you would remove YEAR(@cell)= and swap out {Created} for your {Created year helper} column.

I also just noticed that you're using AVERAGE, not AVG, which could also be causing your error.

Philip Robbins

I expect it's the YEAR({Created}) part that's causing the error, but I do wonder if the way you're handling months also needs fixing, as no row is going to be all three months at once.

I would change it to:

=AVG(COLLECT({My provider treated me with respect}, {Month}, OR(@cell=4, @cell=5, @cell=6), {Created}, YEAR(@cell)=YEAR(TODAY()))) * 0.2

Note you might want a helper column for the year of your Created column, at which point you would remove YEAR(@cell)= and swap out {Created} for your {Created year helper} column.

I also just noticed that you're using AVERAGE, not AVG, which could also be causing your error.

Amy Swan

This worked! Thank you so much!!!