In a spreadsheet I am working with, I am trying to come up with a formula to calculate an average, based on several variables. I want to know how many times any data (the data contained isn't important for this average) appears in row #21, then average it against the total number of rows containing any data. To give a little substance to this calculation, I am looking at employee evaluations. I want to know how often an employee, during their training phase, takes initiative on their own (reflected by a given evaluation in row #21 labeled, "self inititation") versus the total number of opportunities to do so (as reflected by the total number of rows containing any evaluation in all categories). For example, an employee comes to work and without being told to do so acts on a certain task, but then the very next task, they do because their supervisor tells them to. So in their evaluation, they would be given a rating in the "self initiation" category on the first task, but not on their second, therefore there would be no rating in that category but possibly other categories. So this employee's average in self initiation so far is 50%.

Can someone help? I was previously working with the following formula when the total number of tasks completed was manually input in a particular cell of the worksheet (L2). I want to have a formular count the total, so it doesn't have to be manually done.

=IF(ISERROR(AVERAGE(COUNT(B21:M21))/L2),"0%",AVERAGE(COUNT(B21:M21)/L2))

Thanks in advance!