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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.