Average or Average If with Sheet References

Tina Rustvold
Tina Rustvold ✭✭✭✭✭
edited 03/01/24 in Formulas and Functions

I tried using a formula created by the Generator, using cross sheet references to pull the data into our metric sheet, and received several errors. We would like to receive an average capacity percentage for an individual PM; the Generator provided the formula below, but if I inserted sheet references in lieu of column names it returns Unparsable.

=IF([IM Lead]@row = "Amanda", AVG([Jan '24]@row:[Dec '24]@row), "N/A")

If I use and AVERAGEIF formula with the sheet references, I get closer with an Incorrect Argument error. I feel like I need an @cell or @row somewhere, but when I try to throw that in it becomes Unparsable. Which is making me think this may be impossible...



  • AravindGP
    AravindGP ✭✭✭✭✭

    Hi @Tina Rustvold

    Smartsheet can only do Average or Sum formulas (and the different variants of them) across multiple rows but a single column as range. So, if you have for multiple projects as rows and just wanted to take an average of January for Amanda, the Averageif will work. Since you have a set of columns as your range for the calculation, you're not getting the result.



    Reach out for any help on licenses, configuration, or training

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!