Using the average and standard dev formula

Options

I am trying to use an average formula to get the average of data from the past 30 days. I tried using collect but seem not to be working as it gives me an error. I also am using standard dev formula both for the past 30 days/data before that row. Please help.

Best Answer

  • Darion Albert Roque
    edited 04/13/22 Answer ✓
    Options

    My apologies, I was able to figure it out to get the average from the past 30 days, however what I actually need was last 30 entries. This is the formula that I got to work.

    =AVG(COLLECT([Tensile Strength]:[Tensile Strength], [Test Date]:[Test Date], <=TODAY(), [Test Date]:[Test Date], >=TODAY(-30)))


Answers

  • Katy H
    Katy H ✭✭✭✭✭✭
    Options

    Can you please show the formula and the error message you are using/seeing to help diagnose?

    Typically using collect would look like this:

    =AVG(COLLECT({range you want to average}, criterion range 1, criterion 1, criterion range 2, criterion 2...)

    Hope this helps!

    Katy Hall

    Head of Product Management

    ILLA Canna

    LinkedIn

  • Darion Albert Roque
    edited 04/13/22 Answer ✓
    Options

    My apologies, I was able to figure it out to get the average from the past 30 days, however what I actually need was last 30 entries. This is the formula that I got to work.

    =AVG(COLLECT([Tensile Strength]:[Tensile Strength], [Test Date]:[Test Date], <=TODAY(), [Test Date]:[Test Date], >=TODAY(-30)))


  • Katy H
    Katy H ✭✭✭✭✭✭
    Options

    Glad it worked out!

    Katy Hall

    Head of Product Management

    ILLA Canna

    LinkedIn

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!