Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
krl - ExcelForums.com
 
Posts: n/a
Default Weighed Average of a weiged average when there are blanks

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;D1:D5)/SUMIF(D1:D5;"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! :)

  #2   Report Post  
Bernard Liengme
 
Posts: n/a
Default

It is not clear which column have credits and which have grades.
Problem 1:
=SUMPRODUCT(C1:C5;D1:D5)/SUMIF(D1:D5;"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;D1:D5)+ SUMPRODUCT(next set)+SUMPRODUCT(next
set))/(SUMIF(D1:D5;"0";C1:C5)+sumif(nextset)+SUMIF(next set))

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"krl - ExcelForums.com" wrote in
message ...
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;D1:D5)/SUMIF(D1:D5;"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! :)



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Weighed average if the adjecent column contains blanks Kasimir Lehväsl Excel Discussion (Misc queries) 2 July 4th 05 10:12 PM
What is this kind of average called? havocdragon Excel Worksheet Functions 3 June 24th 05 05:10 PM
Average Formula with Criteria PW11111 Excel Discussion (Misc queries) 1 June 10th 05 02:22 PM
AVERAGE and STDEV functions with logic t-rung Excel Worksheet Functions 1 May 26th 05 07:11 PM
average function in Excel 2002 Sherry New Users to Excel 13 May 8th 05 01:49 PM


All times are GMT +1. The time now is 09:44 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"