AVGWIF

Options

Hello,

I am trying to create a formula which will calculate the average weight of a series of tasks associated with a specific category. Basically, a SUMIF but instead of a sum of the numbers, an average weight of the numbers. Here is a sample of my data.

All numbers in green are manually entered as progress occurs. The weight is the "Expected Duration (Hours)" column. I would like a formula that calculates the weighted average of progress for only the tasks associated with the "Analyzers & Instrumentation" category. Any help would be much appreciated!

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    You're absolutely right. Try:

    =SUMIF([category]:[category], "Analyzers & Instrumentation", [weight]:[weight])/ SUMIF([category]:[category], "Analyzers & Instrumentation", [expected duration (hours)]:[expected duration (hours)])

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 04/05/21
    Options

    Hi @Delcie Thankachan ,

    I edited my initial response. Try:

    You'll need to use a helper column. Insert a text/number column [weight] with the formula =[Expected Duration (Hours)]@row * [tech 1 Training Progress]@row

    For your weighted average use the formula:

    =SUMIF([category]:[category], "Analyzers & Instrumentation", [weight]:[weight])/ SUMIF([category]:[category], "Analyzers & Instrumentation")

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Delcie Thankachan
    edited 04/05/21
    Options

    Hi Mark,

    Thank you for your suggestion. On first try it is kicking out an "#INVALID OPERATION" error. I resolved the issue, but when all tasks are at 100% the formula

    =AVERAGEIF([category]:[category], "Analyzers & Instrumentation", [weight]:[weight])

    gives a result of 135%. I won't deny math isn't my forte, so I'll keep exploring why this may be the cause.

    -Delcie

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi @Delcie Thankachan ,

    I edited my initial response. Does the change work for you?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Delcie Thankachan
    Options

    Hi Mark,

    The new formula

    =SUMIF([category]:[category], "Analyzers & Instrumentation", [weight]:[weight])/ SUMIF([category]:[category], "Analyzers & Instrumentation")

    results in a #DIVIDEBYZERO error. I believe because the divisor is trying to sum a text field ("Analyzers & Instrumentation"). Did you mean to insert a different function?

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    You're absolutely right. Try:

    =SUMIF([category]:[category], "Analyzers & Instrumentation", [weight]:[weight])/ SUMIF([category]:[category], "Analyzers & Instrumentation", [expected duration (hours)]:[expected duration (hours)])

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Delcie Thankachan
    Options

    It feels so close I can almost touch it! In order to test the formula,

    =SUMIF([category]:[category], "Analyzers & Instrumentation", [weight]:[weight])/ SUMIF([category]:[category], "Analyzers & Instrumentation", [expected duration (hours)]:[expected duration (hours)])

    I selected all "Analyzer & Instrumentation" tasks and marked them 100% complete. When doing so, the formula resulted with 0.98347. I would expect that it should result with the answer 1.

    I confirmed the weight column was working properly (when all tasks are 100% complete, [weight] is identical to [expected duration (hours)])

    I'll keep tweaking on this side, but if you have any suggestions from the information at hand, please let me know! Thank you for your help!

  • Delcie Thankachan
    edited 04/14/21
    Options

    It worked! The minor error was a missing data point. Thank you so much for your help Mark!

    The correct solution to this question is

    =SUMIF([category]:[category], "Analyzers & Instrumentation", [weight]:[weight])/ SUMIF([category]:[category], "Analyzers & Instrumentation", [expected duration (hours)]:[expected duration (hours)])

  • ilyas star
    Options

    Hi mark

    iam in searching for the formula like excel Unique() Thats get the unique names

    sample

    table

    Apple

    Apple

    Orange

    Orange

    After use function

    Apple

    orange

    thanks

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi @ilyas star ,

    There isn't an equivalent formula available in Smartsheets. You should submit an enhancement request:

    Someone else may have a creative way to replicate the Unique function using available Smartsheet capability.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!