Average Formulas

Options

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

Best Answer

Answers

  • megan.griffiths
    megan.griffiths ✭✭✭✭
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!