We help IT Professionals succeed at work.

weigthed average in Excel spreadsheet

LeTay
LeTay asked
on
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)
Comment
Watch Question

Top Expert 2015
Commented:
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...
Jeff DarlingDeveloper Analyst

Commented:
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.
Jeff DarlingDeveloper Analyst

Commented:
Most Valuable Expert 2013

Commented:
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

Author

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(..) ?
Top Expert 2015

Commented:
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...

Author

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)
Top Expert 2015

Commented:
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...

Author

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 ...
Top Expert 2015

Commented:
You can use this formula...

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

Open in new window


Again entered  by ctrl+shift+enter..

Author

Commented:
Excellent !
By the way, what is the effect of ctrl-shift-enter ?
Top Expert 2015

Commented:
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...

Author

Commented:
Many many thanks