AVG Collect Formula Help

PaigeKucz

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

ShelbyWarren

@PaigeKucz Here's one solution:

1. 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.
2. 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.