How to get an "Average" of statuses
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 subgoals and several projects tied to these subgoals, 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 subgoal 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!
Answers

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?
Amber Eakin, MSLS, M.Ed.
Adult Education Specialist  Process Improvement Enthusiast

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

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!
Amber Eakin, MSLS, M.Ed.
Adult Education Specialist  Process Improvement Enthusiast

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.
Help Article Resources
Categories
Check out the Formula Handbook template!