weigthed average in Excel spreadsheet

I already asked something similar.
It was just to now how to calculate a weigthed average in Excel
So now I have this in some cells :
The "weights" are in column A2...An
The "scores" are in column C2...Cn for person C, D2..Dn for person D etc...
The weighted averages are in  A1..X1 using this formula (thanks to EE !), for example for person C
sumprod($A2:$An;C2:C100)/sum($A2:$A100)

But now I come with something more complex to calculate !
There are now two "weights column, says A and B
All scores are always positive but I put a score positive, if its weight is in column A and as negative (a tric) if in fact it is of course positive, but the weight is in column B !
How now to calculate the weigthed averages
Example
A       B       C
2       3        50
1       4       -40
The weigthed average for column C is ((2 * 50) + (4 * 40)) / (2 + 4)
LVL 1
LeTayAsked:
Who is Participating?
 
Saurabh Singh TeotiaCommented:
You can do this by using 2 sumproducts like this...

=SUM(SUMPRODUCT((C2:C3>0)*(A2:A3)*(C2:C3)),SUMPRODUCT((C2:C3<0)*(B2:B3)*ABS(C2:C3)))/SUM(SUMPRODUCT((C2:C3<0)*(B2:B3)),SUMPRODUCT((C2:C3>0)*(A2:A3)))

Open in new window


And on Second thoughts..you can combine them in 1 only which is this...

=SUMPRODUCT(((C2:C3>0)*(A2:A3)*(C2:C3))+((C2:C3<0)*(B2:B3)*ABS(C2:C3)))/SUMPRODUCT(((C2:C3<0)*(B2:B3))+((C2:C3>0)*(A2:A3)))

Open in new window


Saurabh...
0
 
Jeff DarlingDeveloper AnalystCommented:
One way to do it would be to add another column, with an IF statement to look at the value to see if it is negative or not, then store the appropriate value.

Sample attached.
0
 
Jeff DarlingDeveloper AnalystCommented:
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
barry houdiniCommented:
Another possible approach is to use an "array formula" like this

=SUM(ABS(C2:C3)*IF(C2:C3>0,A2:A3,B2:B3))/SUM(IF(C2:C3>0,A2:A3,B2:B3))

confirm with CTRL+SHIFT+ENTER

regards, barry
0
 
LeTayAuthor Commented:
Second formula is wonderful
A last question : how to obtain the average of the ABS values of a column with creating additional columns (contains abs(..) ?
0
 
Saurabh Singh TeotiaCommented:
Letay,

I'm not sure that i follow you on your question..Can you again show sample of the data like you said in original question..

Saurabh...
0
 
LeTayAuthor Commented:
Well, very simple (second question)
A column contains for example these value :

50
40
-50
20
-30

I want to place below the last value the average of the absolute values above, without using a second column with all values set as positives (with the ABS function)
In the example the result should be 38 (average of 50 40 50 20 30)
0
 
Saurabh Singh TeotiaCommented:
Simply write this formula...

=AVERAGE(ABS(A1:A5))

Open in new window


Now post when you write this formula..enter it with ctrl+shift+enter rather then normal enter

This will do what you are looking for..

Saurabh...
0
 
LeTayAuthor Commented:
Works "allmost" : in the range, there are empty cells
The formula you propose seems to take these into account
I want these empty cells not participating in the average ...
0
 
Saurabh Singh TeotiaCommented:
You can use this formula...

=AVERAGE(IF(A1:A15<>"",ABS(A1:A15),""))

Open in new window


Again entered  by ctrl+shift+enter..
0
 
LeTayAuthor Commented:
Excellent !
By the way, what is the effect of ctrl-shift-enter ?
0
 
Saurabh Singh TeotiaCommented:
Ctrl+Shift+Enter means that formula is an array formula by that i mean it reads each range in your array one by one combined with an aggregate function..

Normally when you apply an if function you can't apply on a range you just applied on a single cell only but ctrl+shift+enter reads that formula as an array formula and does the necessary calculations...
0
 
LeTayAuthor Commented:
Many many thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.