7 Replies Latest reply: Aug 24, 2012 6:02 AM by Stefan Wühl

# EXCEL Sumproduct Functionality

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?

CourseScoreWeightWeight AVG
Calculus54
Discr. Math43
English Lit.42
Chemistry42
Comp. Sci.53

Thanks very much.

• ###### Re: EXCEL Sumproduct Functionality

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.

• ###### Re: EXCEL Sumproduct Functionality

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

CourseScoreWeightWeight AVG
Calculus545
Discr. Math434
English Lit.424
Chemistry424
Comp. Sci.535

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

• ###### Re: EXCEL Sumproduct Functionality

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)

• ###### Re: EXCEL Sumproduct Functionality

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?

• ###### Re: EXCEL Sumproduct Functionality

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)

• ###### Re: EXCEL Sumproduct Functionality

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

CourseScoreWeightWeight AVG
Calculus545
Discr. Math434
English Lit.424
Chemistry424
Comp. Sci.535
• ###### Re: EXCEL Sumproduct Functionality

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