0

I have produced a set of data using a countif as exampled below:

=COUNTIFS(Matrix!$C6:$EZ6,"1",Matrix!$C$5:$EZ$5,"Attainment",Matrix!$C$3:$EZ$3,AD$3)

What this is doing is looking at a students grades against a subject and calculating the count of that grade. I have 5 grades, *, 1, 2, 3, 4, so I have 5 sets of calculated columns for each subject. What I am now trying to do is a weighted sum using the results of these countif's. * = 0, 1=1,2=2 etc, however students can get more than 1 grade per subject so I need to average these.

Student X got two 2 grades for biology and one 3 grade, I need to do (2x2/2) + (1x3/3) = 2.3

I have tried 

=SUM((F4*0)+(AG4*1/AG4)+(BH4*2/BH4)+(CI4*3/CI4)+(DJ4*4/DJ4))

However, I get a divide by zero error as not all cells hold a value above zero.

Is there a way to create a formula that says if the cell value is greater than zero then do the calculations, but if not ignore. I can't do a nested if. So, if the sum of AG4*1/AG4 results in a number then add this, but if it produces a zero, do not and move on and do the same for the next calculation. I can't do a nested if as more than one calculation may return a positive value.

MIS_Gem
  • 117
  • 9
  • 1
    Well, you have the logic as per your countifs(), so now use sumifs()... – Solar Mike Oct 26 '18 at 11:08
  • Can you please give some mock example of your data as a table or picture... easier to understand your setup and how you have implemented your functions. – Wizhi Oct 26 '18 at 11:36

1 Answers1

0

Your weighting formula seems wrong. You wrote '(2x2/2) + (1x3/3) = 2.3'. However, (2x2/2) + (1x3/3) returns 3.

Shouldn't it be (2x2) + (1x3) / (2+1) = 2.3 ?

Try using the following formula:

=(F4*0)+(AG4*1)+(BH4*2)+(CI4*3)+(DJ4*4) / SUM(F4,AG4,BH4,CI4,DJ4)

You can use wrap it in an IFERROR function to avoid errors for students with no grades

Mike
  • 144
  • 10