Finding the average percentage from countif of achievement perecentages

[anuradhagrewal]

Hi
I need your help on the following excel file
Attached is the link to the excel file

Principal KRA.xlsx

Sheet1 PRINCIPALS OBJECTIVES,ACHIEVEMENT PERCENTAGE 25%,50%,75%,100% I,Develops, Advocates for, and Enacts a Shared Vision for High Student Achievement Supported by a Culture of High Expectations and Family Engagement A.,Develops and enacts a shared vision for high achievement and post-secondar...

docs.google.com

I need the following things to be done on this excel sheet and seek your kind consideration

• There is an achievement percentage written in row 3
• If a person has achieved say in cell C5 25% I want the evaluator to put "Y" in D5 and subsequent E6,F6, and D6 become blank or cannot be populated
• This is to be continued so on and so forth in all the marked cells till row 29
• Then I am wondering that after I have "Y" for say in column D I can get a count of all using COUNTIF.
• What I am not able to understand is how to give a final score

I am very confused
My confusion is that if say the evaluator give "5 Y" in column D which is 25% achievement and so and so forth how do I calculate the total percentage of achievement. There are total 21 parameters according to which a person is to be evaluated.

Please let me know how do I get an average overall percentage of achievement considering the column D, column E, column F, column G
Will it be a weighted average
Please guide

Anu

 

[rondeondo]

Hi Anu,
If I correctly understand your scoring system then an overall score would relate to the number of time each score was given out of a total score available.
So if you have 21 criteria with a score of 25%, 50%, 75% or 100% based on the position of the Y, and if each criteria is equally weighted then you'd want to take the number of Y's in column D x 25% then divide by 21 for the number available.

=(countif(D5:D29,"Y")*0.25 + countif(E5:E29,"Y")*0.5 +countif(F5:F29,"Y")*0.75 +countif(G5:G29,"Y") )/21

So to check the logic, if you scored 100% for each of 21 criteria, you'd get 21/21 =100%. If you got all 25% options you'd have 21*0.25/21 = 25%
So yes a weighted average.
As for locking the other cells in a row after entering a Y, that's more of a macro. You could add an error message per row that showed if the count of "Y" was >1 for the row.
Hope this helps

 

[anuradhagrewal]

Hi Anu,
If I correctly understand your scoring system then an overall score would relate to the number of time each score was given out of a total score available.
So if you have 21 criteria with a score of 25%, 50%, 75% or 100% based on the position of the Y, and if each criteria is equally weighted then you'd want to take the number of Y's in column D x 25% then divide by 21 for the number available.

=(countif(D5:D29,"Y")*0.25 + countif(E5:E29,"Y")*0.5 +countif(F5:F29,"Y")*0.75 +countif(G5:G29,"Y") )/21

So to check the logic, if you scored 100% for each of 21 criteria, you'd get 21/21 =100%. If you got all 25% options you'd have 21*0.25/21 = 25%
So yes a weighted average.
As for locking the other cells in a row after entering a Y, that's more of a macro. You could add an error message per row that showed if the count of "Y" was >1 for the row.
Hope this helps

Thank you Sir
As for locking the other cells in a row after entering a Y, that's more of a macro. You could add an error message per row that showed if the count of "Y" was >1 for the row.

I have no idea how to do this can u please help me out

Regards

Anu