Average Formulas

megan.griffiths

Hi all,

I need some help with average formulas please. I have a sheet that contains the following relevant columns:

• Date Received (dd/mm/yyyy)
• Business Type (Investments, Pensions, Mortgages etc)
• Amount Invested
• Initial Fee
• Initial Fee % (calculated from Amount invested and initial fee)
• Date Processed (dd/mm/yyyy)
• Case Owner

I need a formula to calculate the average initial fee by month, only including a few business types (Investments, Pensions, Mortgages). I would also like the average initial fee for the last 12 months as a stand-alone value.

I have got the Average formula to work for November:

=AVG(COLLECT({Initial Fee}, {Date Received}, IFERROR(MONTH(@cell), 0) = 11, {Date Received}, IFERROR(YEAR(@cell), 0) = 2020))

But I now need to put in the condition that the Case Owner is NOT "Returned", and the Business Type IS "Investments", "Pensions" or "Mortgages". Can anyone help please?

Thanks in advance,

Meg

Mark Cronk

Hi @megan.griffiths ,

Keep adding ranges and criteria to your collect statement. Remember, all conditions must be met for the Initial Fee to be included in the calculation.

=AVG(COLLECT({Initial Fee}, {Date Received}, IFERROR(MONTH(@cell), 0) = 11, {Date Received}, IFERROR(YEAR(@cell), 0) = 2020, {Case Owner}, @cell<>"Returned", {Business Type}, OR(@cell="Investments", @cell="Pensions", @cell="Mortgages")))

Work?

Good luck,

Mark

Mark Cronk

Hi @megan.griffiths ,

Keep adding ranges and criteria to your collect statement. Remember, all conditions must be met for the Initial Fee to be included in the calculation.

=AVG(COLLECT({Initial Fee}, {Date Received}, IFERROR(MONTH(@cell), 0) = 11, {Date Received}, IFERROR(YEAR(@cell), 0) = 2020, {Case Owner}, @cell<>"Returned", {Business Type}, OR(@cell="Investments", @cell="Pensions", @cell="Mortgages")))

Work?

Good luck,

Mark

megan.griffiths

@Mark C. Thanks very much Mark - that worked perfectly!