Need Gradebook Help (unsure of how to describe problem)

**calebjcook** · 02-09-2015, 12:04 PM

(working from the title)

I have 16 cells in my gradebook for Participation. One cell is for one class.
The value of each set can be set by the teacher and the total percent for Participation is divided equally among the number of the 16 cells.

If Participation is worth 20%, and a teacher decides to give each class a value of 2 and has only 13 classes, then each cell is 1.53%. This part is easy and how the gradebook used to work.

The problem begins with teachers requesting the ability to set variable percentages for each class (instead of having the total split equally).

For example (rows, top to bottom: week; student score; perfect score; weighed values):

Please Login or Register  to view this content.

In the case of wk 09, the student has 0.00, but the Participation for that class was 4% of the 20% total. The old SUM formula won't work—I'm not sure what will and I'm not sure if there's a word or phrase for this problem.

I need a way to properly calculate weighted percentages for any of the 16 cells against whatever value has been recorded for the student and have the weight values properly deduct from the total percentage for Participation.

I also need a solution that works from Excel 2003 up.

Any help or pointers would be greatly appreciated.

Thank you.

**Pauleyb** · 02-09-2015, 06:07 PM

Next time, please include your spreadsheet. It will allow people to use your actual cell references and data.

I think you are looking for SUMPRODUCT. Lucky for you, it arrived in Excel 2003. It is generally smart enough to ignore non-numbers, but if I understand your needed calculation, then you need to do a weighted average (weighted values) of the students' participation averages (student score/perfect score). Division does not like non-numbers, so I had to have it ignore the text. I'll assume 'wk 01' is in cell A1:

=SUMPRODUCT(IF(ISNUMBER(A2:P2),A2:P2,0)/IF(ISNUMBER(A3:P3),A3:P3,1),A4:P4)

This is an array formula, so use Ctrl-Shift-Enter (instead of just Enter) when you enter it into your cell.

**calebjcook** · 02-09-2015, 09:40 PM

Hi Pauleyb!

Your suggestion works fine and produces the results I needed. Thank you.

I kept working last night and came up with a way to do this, but it adds a lot of stuff and isn't as clean

I broke the sheet out of the workbook (and hopefully all the info is there

) and attached so folks can see both solutions.

Additional feedback is appreciated!

demo2.xls