AVGWIF

Delcie Thankachan

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!

Mark Cronk

You're absolutely right. Try:

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

Mark

Mark Cronk

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

Delcie Thankachan

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

Hi @Delcie Thankachan ,

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

Mark

Delcie Thankachan

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

You're absolutely right. Try:

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

Mark

Delcie Thankachan

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

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

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

Hi @ilyas star ,

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

https://app.smartsheet.com/b/form/739aa75f30ca43a8a22eb53e4da7d409?ss_v=126.0.1

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

Mark