How to get an "Average" of statuses

Jona_g28

Hi team,

I am trying to find the best formula to get an average of statuses. I have a helper sheet build out for this purpose. I have certain sub-goals and several projects tied to these sub-goals, each of the projects has a status - not started, in progress and complete. I am trying to find a formula where out of these projects that fall under the same sub-goal category, I want the average status. Let's say I have 4 Not Started, 1 in Progress and 2 Complete - I want to know what's my average out of these. Which formula would be best to use in this case? I tried Averageif but it doesn't seem to be working. Any ideas are very appreciated!

Amber Eakin

Hi @Jona_g28 ! What's "average"? Do you mean the median (midpoint) or the mean (sum of numbers divided by the count)?

First, I'd recommend setting up a helper column to assign a point value to each of the statuses. For instance, you might say "Not Started" is 0, "In Progress" is 1, and "Complete" is 2.

=IF(Status@row = "Not Started", 0, IF(Status@row = "In Progress", 1, IF(Status@row = "Complete", 2)))

Then, you can have another column for the average status of the subgoals.

=IF(AVG(CHILDREN(Helper@row)) < 1, "Not Started", IF(AVG(CHILDREN(Helper@row)) < 2, "In Progress", IF(AVG(CHILDREN(Helper@row)) = 2, "Complete")))

With your example, it looks like this:

When more are marked as "In Progress", the average status updates.

Would this work for you?

Jona_g28

Hi Amber,

Thanks for your reply. I am looking for the mean actually. I was looking for some sort of way to have it setup without the helper column because my main sheet has more than 20 columns already and I am doing these calculations on a separate sheet. Would there be any other way to have it setup?

Thanks

Amber Eakin

Hi @Jona_g28 - AVG and AVERAGEIF are only going to work with numbers, not words. You could put the Helper column in the calculation sheet. Another option is to do an IF formula based on COUNT of statuses, but that will likely only work if you have the exact same subgoals for each larger goal.

Maybe someone else will have an idea that doesn't use a helper column. Fingers crossed!

Jona_g28

What about using AVG COLLECT column? would that be of any help?

for the time being, I have 100+ rows on my sheet and planning on adding more. Perhaps I could do linking but that sounds like a lot of work to maintain manually since I can't link empty rows.