Average grade from two columns

[LolaOO]

[TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD]2[/TD]
[TD]10%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5%[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]35%[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]27%[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]23%[/TD]
[/TR]
</tbody>[/TABLE]
I have these two columns in Excel file. The first one with grades, the second with percent how many students get that grade. How to calculate average grade from this data?

 

[Rick Rothstein]

Re: Avarage grade from two columns

Assuming your data starts on Row 2 where I have assumed Row 1 contains header, I believe (but do not know for sure) that this formula will return the average grade...

=SUMPRODUCT(A2:A6/B2:B6)/SUM(A2:A6)

 

[Dave Patton]

Re: Avarage grade from two columns

=sumproduct(a2:a6,b2:b6)/sum(a2:a6)

 

[Rick Rothstein]

Re: Avarage grade from two columns

=sumproduct(a2:a6,b2:b6)/sum(a2:a6)

That yields an average grade of 0.233077 which cannot be right as the average grade when the lowest grade is 2 and the highest grade is 9. I am pretty sure the formula I posted in Message #2 is correct (using division instead of your implied multiplication) as it yields an average grade of 6.128541 which seems to be about right to me (the OP will have to decide if he wants to round that for a whole number average or not).

 

[Dave Patton]

Re: Avarage grade from two columns

I misread the information.

Try

=SUMPRODUCT(A2:A6,B2:B6*100)/(SUM(B2:B6)*100)

 

[Marcelo Branco]

Re: Avarage grade from two columns

Maybe...

I was thinking something like that (weighted average)
=SUMPRODUCT(A2:A6,B2:B6)/SUM(B2:B6)

But since the sum B2: B6 must always be 1, we can shorten to
=SUMPRODUCT(A2:A6,B2:B6)

M.

 

[Dave Patton]

Re: Avarage grade from two columns

Marcelo's suggestions is much better.