Urgent Excel Average Formula Help 2

[SteveAudus]

I have a set of 400 pupil results, for 19 subjects
laid out on an excel sheet.

Quick Example:

Pupil | Subject1 | Subject2 | Subject 3 | Average Grade
John 3 4 sum(B2:B4)/3
Mick 4 sum(C2:C4)/3
Steve 2 3 sum(D24)/3

My problem is the Average Grade is incorrect
because certain pupils are entered for some subjects
and no others. The Sum must be divided only by
the cells that have a entry in them.
So "John" should be sum(B2:B4)/2

But there must be a quick of doing this, I have
400 pupil to do it on.

Can anyone help!!
What is the Formula???

Thank You
Steve Audus
Chaucer Community School
Sheffield
UK

 

[ETID]

this formula treats zeros and blanks as not being there

=SUMIF(B2:B4,">0&quot/COUNTIF(B2:B4,">0&quot


this formula will count blanks as zeros and include them in the average

=Average(B2:b4)

 

[jebry]

Hi Steve!

Divide by Count(B2:B4) instead of three. Of course you will get an error if the student is not in any class. If you want to avoid that use this to divide:
If(Count(B2:B3)=0,0,Sum(B2:B4)/Count(B2:B4))

hth
Jeff Bridgham
bridgham@purdue.edu

 

[mbarron]

Steve,

You can use the AVERAGE function to solve your problem.
=AVERAGE(B2:B4)
The AVERAGE function essentially is: SUM(of numbers in range)/COUNT(of cells that contain numers)

Mike

 

[Hasit]

The way I would approach this is in 3 steps. The first is to do a sum for each student. The second is to work out how many exams the student is sitting, and third step is to divide the total for each student by the number of exams they have sat. You will need three columns: Total Grade, Total number of Exams, Average Grade added to the right of your data.

So. Step 1. Using the SUM formula as you have done, total the entry for each student. The result should go in the "Total Grade" for each student.

Step 2. Use =COUNT(B2:B4) to give you the number of exams the students have sat. The result should go "Total Number of Exams" for each student.

Step 3. Divide the result of step 1 by the result of step 2 and you should get the average for each student.

 

[Guest_imported]

THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU

Hasit's solution worked best,

Cheers all,
Steve