Hi all…
Not sure if this is really complicated (maybe an over-engineered solution or over-explained question??) or if it’s just a lack of understanding on my part that makes it feel more complex than it really is but here goes…
I’ve got a worksheet I’m using to manage the implementation of a strategic plan. At this stage, I’m capturing the insights, from our implementation group, on how they think each action scores against a number of criteria - so for example if an action’s current need for person A is low, it’s gets a score of 2 but for person B, it’s medium so it gets a score of 3. Scores are 1-5, very low to very high. From this, I take the total of all scores given and divide by 6 (the number of people in the implementation group) to give me an average score. This average score is then displayed using the same very-low to very-high scale. By the end of it all, I want to have a list of priorities that are ranked based on our key criteria, i.e. current need, that was calculated as subjectively as possible. The challenge I’m having comes with my use of parent and child rows.
My team will only be scoring the most granular actions, i.e. not parent or grandparent actions, but I’m still trying to calculate the average score for these parent and grandparent actions, based on the scores provided beneath them (see what I mean about complex 
) The formula below is working fine, but it requires all child cells to have data before any average is displayed in the parent, or grandparent, cells.
To try and explain what’s going on here:
- D5 is the logical expression that determines an action has no children
- All those IFS are basically adding all the scores from my team, depending on their selection
- /6 is getting the average of the total scores
- SUM (CHILDCELLS())… is trying (but failing) to give a an average score for of all child actions under the same parent
=IF(D5=0, (IFS(G5="Very Low",1,G5="Low",2,G5="Medium",3,G5="High",4,G5="Very High",5) + IFS(H5="Very Low",1,H5="Low",2,H5="Medium",3,H5="High",4,H5="Very High",5) + IFS(I5="Very Low",1,I5="Low",2,I5="Medium",3,I5="High",4,I5="Very High",5) + IFS(J5="Very Low",1,J5="Low",2,J5="Medium",3,J5="High",4,J5="Very High",5) + IFS(K5="Very Low",1,K5="Low",2,K5="Medium",3,K5="High",4,K5="Very High",5) + IFS(L5="Very Low",1,L5="Low",2,L5="Medium",3,L5="High",4,L5="Very High",5))/6, SUM(CHILDCELLS(M5)>0)/COUNT(CHILDCELLS(M5)>0))))
If anyone can help it’d be great…even if it is to tell me I’m nuts for making this so complicated.