AVG Collect Formula Help

I am looking to add to the formula so that if {APD CI Tracker Range 5}, ="Pilot/ Implemented" the Dashboard reads "Pilot" OR if {APD CI Tracker Range 5}, ="Monitor/ Check" the dashboard reads "Monitor" and if {APD CI Tracker Range 5} is not Working, Pilot/ Implemented, or Monitor/ Check the Dashboard reads 0

Current Formula: =AVG(COLLECT({APD CI Tracker Range 9}, {APD CI Tracker Range 11}, ="Problem Solving Group 4", {APD CI Tracker Range 5}, ="Working"))

Find more posts tagged with

Formulas

Dashboards

Accepted answers

All comments

ShelbyWarren

@PaigeKucz Here's one solution:

Add a column to your sheet that I'll title "Project Status", and place an INDEX COLLECT formula in it that references the "{APD CI Tracker Range 5}" to return the Project Status result.
Add this IF statement formula in the column you're using for the Dashboard result: =IF([Project Status]@row = "Pilot/ Implemented", "Pilot", IF([Project Status]@row = "Monitor/ Check", "Monitor", IF(AND([Project Status]@row <> "Working", [Project Status]@row <> "Pilot/ Implemented", [Project Status]@row <> "Monitor/ Check"), 0)))

PaigeKucz

@Shelby Warren that didnt work - it doesnt capture Problem solving Group 4 # of days with that formula

ShelbyWarren

@PaigeKucz Can you add screenshots of the sheets/columns you're referencing? (hide sensitive data)

PaigeKucz

what would the index collect formula be that is what I am struggling with @ShelbyWarren

ShelbyWarren

@PaigeKucz Would you be able to share screenshots of your sheets? If there's no sensitive data involved, maybe share the sheets with me - swarren@patriotdevelopment.com

I'd be happy to take a look and add the formulas.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.