AVG(IF) statement based on criteria

Hi Team, Can you please help me write a formulae wherein each KPI has 4 options in dropdown value each representing a certain value as below

Partially Met = 100%, Met = 100%, Not Met = 0%

How do bring an average value under Score based on the selection in the three KPI. I have entered the Score %s for your reference so the formulae output can match the same.

Can you please help.

Find more posts tagged with

Accepted answers

All comments

CodyRussell

Trying using this:

=AVG(IF([KPI - A]@row = "Partially Met", 1, IF([KPI - A]@row = "Met", 1, IF([KPI - A]@row = "Not applicable", 0))), IF([KPI - B]@row = "Partially Met", 1, IF([KPI - B]@row = "Met", 1, IF([KPI - B]@row = "Not applicable", 0))), IF([KPI - C]@row = "Partially Met", 1, IF([KPI - C]@row = "Met", 1, IF([KPI - C]@row = "Not applicable", 0))))

Gaurav Chauhan

Thank you for sharing the same. There are two elements which have to be considered.

There are 4 dropdown values. Met (100%), Not Met(0%), Partially met (50%) and Not applicable
Considering the selection the range for Average would change, meaning - if 1st and 2nd KPI have some sort of met and 3rd KPI is Not applicable, then the avg would only divide the first two values by 2 and not 3.

Can you please look into the same.

TVang

Hi, @Gaurav Chauhan , this should work,

=IFERROR(SUM(IF([KPI - A]@row = "Partially Met", 0.5, IF([KPI - A]@row = "Met", 1, 0)), IF([KPI - B]@row = "Partially Met", 0.5, IF([KPI - B]@row = "Met", 1, 0)), IF([KPI - C]@row = "Partially Met", 0.5, IF([KPI - C]@row = "Met", 1, 0))) / SUM(IF([KPI - A]@row = "Not Applicable", 0, 1), IF([KPI - B]@row = "Not Applicable", 0, 1), IF([KPI - C]@row = "Not Applicable", 0, 1)), 0)

Explanation...

Take the sum of the status values...("Not Met" = 0)

IF([KPI - A]@row = "Partially Met", 0.5, IF([KPI - A]@row = "Met", 1, 0))
IF([KPI - B]@row = "Partially Met", 0.5, IF([KPI - A]@row = "Met", 1, 0))
IF([KPI - C]@row = "Partially Met", 0.5, IF([KPI - A]@row = "Met", 1, 0))

...and divide by the sum of the applicable statuses...

IF([KPI - A]@row = "Not Applicable",0,1)
IF([KPI - B]@row = "Not Applicable",0,1)
IF([KPI - C]@row = "Not Applicable",0,1)

...wrap it all inside of IFERROR() to catch the potential divide-by-zero error.

If you want to account for when any of the columns is blank, the use...

IF(OR([KPI - A]@row ="", [KPI - B]@row="", [KPI - C]@row=""), "", IFERROR(expression_above))

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.