Average symbols for growing number of columns

celtics345

Hello all,

I know this is likely possible, but I've been struggling to think of the most efficient process. Below is a sheet I'm working on, where trainers enter progress for a trainee in a given "soft skill." These trainer columns will be added automatically on a daily basis via an outside Power Automate flow, while the progress bars in the cells will be added manually.

What I'd like to do is return an average of the symbols between the two Helper columns (which will be hidden and used as the range start and end caps). I'm thinking I'd first need to convert these symbols into numbers (the Formula column). Then, I'd like to convert that number back into an accurate symbol (Averages column). How could we do this? Any help would be appreciated!

Paul Newcome

You would first need to convert the bars into numbers. The easiest way to do this would be to add a series of COUNTIFS together.

=COUNTIFS([Helper 1]@row:[Helper 2]@row, @cell = "Quarter") + (COUNTIFS([Helper 1]@row:[Helper 2]@row, @cell = "Half") * 2) + ......................................

Then you would take that and divide it by the number of cells that aren't blank.

=(COUNTIFS(.....) + (COUNTIFS(.....) * 2) + .....) / COUNTIFS([Helper 1]@row:[Helper 2]@row, @cell <> "")

I would suggest leaving this in its own column because from here we need to use a nested IF. Dropping this into a nested IF will provide for a single column formula, but it will be rather large and unwieldy. Putting the next piece in its own column will make things much easier to manage and troubleshoot.

=IF(Formula@row>= 4, "Full", IF(Formula@row>= 3, "Three Quarter", IF(..............

celtics345

Hi Paul, thanks a bunch for the formulas and explanations! This worked exactly as described. I was planning on splitting the columns anyways, so I'm glad to avoid those unwieldly formulas (I have a few of those elsewhere).

One more request though. How would I go about adding an extra condition to the final formula that rounds up. Basically, if the Formula column (after adding the columns to the left and dividing) adds up to 3.66667, I'd like it to show "Full" (4) in the Averages column. If it's 3.33333 I'd like it to show "Three Quarter" (3). Currently, both of these results in the "Three Quarter" outcome.

Paul Newcome

You would wrap the COUNTIFS in a ROUND function.