Help with Average Formulas

aneves
aneves โœญโœญ
in Formulas and Functions

Hi! I am trying to calculate a 3 months, 6 months and 1 year averages of my team utilization percentage. Each team member is a row and then each month is a column. The utilization percentages are coming from other sheets.

Thank you in advance!

image.png
· Share on FacebookShare on Twitter

Best Answer

  • SSFeatures
    SSFeatures โœญโœญโœญโœญโœญ
    Answer โœ“

    Hi,

    Would something like this work:

    =AVG([08.2024]@row:[June-24]@row)

    This will give the average of the columns 08.2024, 07.2024, and June-24 in your sheet. You can follow this format to calculate the 6 month and 12 month averages as well.

    If you right click a cell in your 3 month average column, then click Convert to Column Formula you can apply the formula to the entire column as well.

    The only downside to this approach is that you will need to update the formula every month when you add new data.

    SSFeatures

    Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com) (LinkedIn)

    SSFeatures makes Smartsheet way easier to use and it saves you hours of work every week. It adds essential features into Smartsheet to save you time. For example: โ€” Auto Sorting โ€” Sorting with Filters โ€” Report PDF Generation โ€” Copy and Paste Conditional Formats โ€” Copy and Paste Automation Workflows โ€” Column Manager โ€” and so many more.

    · Share on FacebookShare on Twitter

Answers

  • SSFeatures
    SSFeatures โœญโœญโœญโœญโœญ
    Answer โœ“

    Hi,

    Would something like this work:

    =AVG([08.2024]@row:[June-24]@row)

    This will give the average of the columns 08.2024, 07.2024, and June-24 in your sheet. You can follow this format to calculate the 6 month and 12 month averages as well.

    If you right click a cell in your 3 month average column, then click Convert to Column Formula you can apply the formula to the entire column as well.

    The only downside to this approach is that you will need to update the formula every month when you add new data.

    SSFeatures

    Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com) (LinkedIn)

    SSFeatures makes Smartsheet way easier to use and it saves you hours of work every week. It adds essential features into Smartsheet to save you time. For example: โ€” Auto Sorting โ€” Sorting with Filters โ€” Report PDF Generation โ€” Copy and Paste Conditional Formats โ€” Copy and Paste Automation Workflows โ€” Column Manager โ€” and so many more.

    · Share on FacebookShare on Twitter
  • aneves
    aneves โœญโœญ

    OMG! it worked! thank you so much!!

    · Share on FacebookShare on Twitter
  • SSFeatures
    SSFeatures โœญโœญโœญโœญโœญ

    You're welcome! I'm glad that it worked!

    SSFeatures

    Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com) (LinkedIn)

    SSFeatures makes Smartsheet way easier to use and it saves you hours of work every week. It adds essential features into Smartsheet to save you time. For example: โ€” Auto Sorting โ€” Sorting with Filters โ€” Report PDF Generation โ€” Copy and Paste Conditional Formats โ€” Copy and Paste Automation Workflows โ€” Column Manager โ€” and so many more.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!