Weighed Average of a weiged average when there are blanks

[krl - ExcelForums.com]

This might be a bit complicated, at least for me it is

I need to calculate a total weighed average of a few weighed averages
of grades

so i have few columns of credits

like:
cr. column1
3
2
5
4
4.5

cr. column2
2
4
3
3
2

cr. column3
2
2
3
3
5

then I have columns of grades between 0 and 5
grd. column1
2
2
(blank)
4
5

grd. column2
1
1
0
(blank)
(blank)

grd. column3
(blank)
(blank)
4
4
4

in some course I havent gotten grades yet so there are blanks

I can get the weighed average of corresponding columns with this kind
of formula:
=SUMPRODUCT(C1:C5;D15)/SUMIF(D15;">0";C1:C5)

the problems are the blanks in some grade columns, while none of the
credit columns have any blanks

now if I want to get the weighed average of the weighed averages
there's a problem. Maybe if there would be a way to make the program
think that the 3 separate columns are one column then I could use the
above technique?

Is it possible or is there some better solution available?

Any help much appreciated!

 

[Bernard Liengme]

It is not clear which column have credits and which have grades.
Problem 1:
=SUMPRODUCT(C1:C5;D15)/SUMIF(D15;">0";C1:C5)
Does it matter about blanks since 6*0=0 so the SUMPRODUCT result remains
correct (same as omitting the row with the blank)
Problem 2;
You might try
=(SUMPRODUCT(C1:C5;D15)+ SUMPRODUCT(next set)+SUMPRODUCT(next
set))/(SUMIF(D15;">0";C1:C5)+sumif(nextset)+SUMIF(next set))

best wishes