Friday, April 26, 2013

yogi_Compute Row By Row Average Of Scores By Two Sets Of Judges And Then The Final Score

                                          Google Spreadsheet   Post  #1156
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI    Apr 26, 2013
user Lindy Bonin :(!category-topic/docs/spreadsheets/z2RhRe8Boi0)

Formula to get the MEAN of a set of data in same row, with a different divisor

I'm in charge of Talent competition/event and have a lot of tabulating of scores to do. To save myself time, I've created a form that I will enter all of the judges scores in (as a grid, value 1-9). 

To get each participants final score, I have to get the average of each individual judge, and then get the average of all the judges scores together. All data is entered into 1 line on the spreadsheet from the form, making it hard for me to get an accurate average or mean of scores. NOT ALL CATEGORIES have the same amount of criteria the judges have to respond to. Example: Vocal solo has only 6 areas the judges will be scoring on, and piano has 7 areas, and choir has 8 areas they are being scored on. So naturally, the formula has to be divisible by the amount of criteria they are being judged on specifically, and not the amount of criteria for another category. 

I need to figure out/build a formula to use in order to keep each participants scoring in the same row... Is there an "IF THEN" statement? Anything?? 

I've attached two screen shots to help better explain my spreadsheet...hope this makes sense.  

ANYONE that can help ASAP would be great. This event is would be a huge time saver if I can pull this off! I will send whatever screen shots, more info if that will help. 

Thanks for your reply. Here is a link to a copy of the spreadsheet.

Thanks for any help you can offer! 

following is a solution based on my understanding of the problem