I need a formula to find the average of the Count of Training IDs

rachmavis

I have a grid that intakes via a form, training evaluation answers. Each row of responses has a TrianingID associated to a specific training.

I have been ask to find the medium average if the number of responses received per trainingID

It needs to be a column formula due the new TrianingIDs populating as more and more trainings send out evaluations.

TraningID Start with BLS21 (one class session)

I attached a screen shot

2024-12-19_14-48-41.jpg

This is what I have so far: Unparsable

=AVG(COUNTDISTINCT([TrainingID]:[TrainingID]))

Michelle Choate 2

From Smartsheet's Training Information:

#UNPARSEABLE

Cause

The formula has a problem which prevents it from being parsed and interpreted. This can happen for many reasons, such as misspelling, incomplete operators, using the wrong case for a column name, or using single quotes instead of double quotes.

Resolution

Ensure that all column names are spelled correctly in cell references, operators are being used correctly, and any text strings in the formula are surrounded by double quotes (" ").

Michelle Choate 2

I can accomplish this counting with 4 extra columns, then the AVG formula in a sheet summary.

The 4 columns I need: Auto, Row ID, TraningID Names, Count of TrainingID

Auto = Auto Numbering

Row ID = MATCH(Auto@row, Auto:Auto, 0)

Training ID Names =IFERROR(IF(Auto@row = 0, "Training ID", INDEX(DISTINCT(TrainingID:TrainingID), [Row ID]@row)), "")

Count of TrainingID = COUNTIF(TrainingID:TrainingID, [Training ID Names]@cell)

Then the final sheet summary formula = AVG([Count of TrainingID]:[Count of TrainingID])