EXCEL Sumproduct Functionality

[#1]

Guys,

I'm trying to use Qlikview to solve a weighted average problem. As the example below,

I would like to get an average GPA of (5*4+4*3+4*2+4*2+5*3)/(4+3+2+2+3)=4.5. How can i do it in QlikView?

Course	Score	Weight	Weight AVG
Calculus	5	4
Discr. Math	4	3
English Lit.	4	2
Chemistry	4	2
Comp. Sci.	5	3

Thanks very much.

[swuehl]

Try using

=sum(Weight*Score) / sum(Weight)

as expression in a table chart with dimension Course.

assuming that you don't need to calculate your Scores / Weights per Course.

[#3]

Thanks, swuehl. I tried it, and it still give me

Course	Score	Weight	Weight AVG
Calculus	5	4	5
Discr. Math	4	3	4
English Lit.	4	2	4
Chemistry	4	2	4
Comp. Sci.	5	3	5

do you know how can i use sum function to get a total of 22?

[swuehl]

You should see your average GPA of 4.5 if you enable the total in expression tab, using expression total.

edit:

Ah, if you maybe want to see the weighted average in every row, use

=sum(total Score*Weight)/sum(total Weight)

[#5]

Thanks, it works. What if 'course' is a group, there are multiple choice, problem solving under each of the courses.

Let's say there are multiple choice with a socre 5, weight 4 and problem solving a score 5 weight4. Then after some calculation, the course socre is 5. if i use sum(total, then it will be 10, not 5 in the table. Do you know how can i solve this?

[swuehl]

Not sure if I can follow your example. But you can add a field list to the TOTAL qualifier, like

=sum(total<Course> Score*Weight)/sum(total<Course> Weight)

[#7]

It gives me the same thing as this table. Thanks very much.

Course	Score	Weight	Weight AVG
Calculus	5	4	5
Discr. Math	4	3	4
English Lit.	4	2	4
Chemistry	4	2	4
Comp. Sci.	5	3	5

[swuehl]

That's probably not what you want? Could you post some sample lines of data together with your expected outcome?