AVGWIF
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

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

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.

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

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

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?

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.

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!

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)])

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

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
Categories
Check out the Formula Handbook template!