AverageIF with non consecutive columns

Options
markkrebs
markkrebs ✭✭✭✭✭✭

trying to do =AVERAGEIF([Jan 2020-SUM-Actual $]@row:[Dec 2020-SUM-Actual $]@row, @cell > 0)

Works awesome unless I move a column into the range set. Anywork around so it's independent of column position? I know I could do a big complex If statement but was trying not make it simple.

Best Answer

  • Ramzi K
    Ramzi K ✭✭✭✭✭
    Answer ✓
    Options

    @markkrebs

    One idea if you have to move columns into the range

    Create another range of columns where cell values equal those of the JAN-DEC range, hide it, and average that on.

    However, the short answer to your question is no. Once you move something into the range it gets included.

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: ramzi@cedartreeconsulting.com

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

Answers

  • Ramzi K
    Ramzi K ✭✭✭✭✭
    Answer ✓
    Options

    @markkrebs

    One idea if you have to move columns into the range

    Create another range of columns where cell values equal those of the JAN-DEC range, hide it, and average that on.

    However, the short answer to your question is no. Once you move something into the range it gets included.

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: ramzi@cedartreeconsulting.com

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!