AVG COLLECT GREATER THAN VALUE

Michelle Maas

I have a formula to calculate the average amount within a sheet. I want to change the formula to only collect and average any amounts that are >£10,000.

=AVG(COLLECT({Archive Sales Amount}, {Archive Sales Stage}, "4 - Closed Won", {Archive Sales Level 1}, "1"))

Thinking I may need IF and @cell but it keeps giving me #UNPARSEABLE.

Leela Lodhi

Hi,
You can modify your formula to include a condition for amounts greater than £10,000 like this:

=AVG(COLLECT({Archive Sales Amount}, {Archive Sales Stage}, "4 - Closed Won", {Archive Sales Level 1}, "1", {Archive Sales Amount}, ">10000"))This will average only the amounts that meet your criteria and are greater than £10,000.

Hope this will help!

Leela Lodhi

Hi,
You can modify your formula to include a condition for amounts greater than £10,000 like this:

=AVG(COLLECT({Archive Sales Amount}, {Archive Sales Stage}, "4 - Closed Won", {Archive Sales Level 1}, "1", {Archive Sales Amount}, ">10000"))This will average only the amounts that meet your criteria and are greater than £10,000.

Hope this will help!

Michelle Maas

Fabulous, that worked great! Thank you @Leela Lodhi

Michelle Maas

@Leela Lodhi To build onto this formula, I have another one that needs to AVG only those in the {Archive Sales Conversion} column where the {Archive Sales Amount} is greater than £10,000:

=AVG(COLLECT({Archive Sales Conversion}, {Archive Sales Stage}, "4 - Closed Won", {Archive Sales Level 1}, "1"))

Leela Lodhi

Hi @Michelle Maas,

You can try the following formula to achieve your requirement:

=AVG(COLLECT({Archive Sales Conversion}, {Archive Sales Stage}, "4 - Closed Won", {Archive Sales Level 1}, "1", {Archive Sales Amount}, ">=10000"))

Let me know if you need further assistance!

Michelle Maas

Fantastic, that worked great. Thanks for your help, @Leela Lodhi