array formula with constant
 

I'm trying to use an array formula to calculate the weighted average.

Gross
A:10
B:20
C:30
D:1000
----------
Total:1060

Rates
A: 15%
B: 10%
C: 30%
D: 85%

How do I use an array forumula to calculate the weighted average rate
based on gross numbers? For example, (10/1060)*15%+(20/1060)*10% and
so on?

Any help is appreciated.


--
name
------------------------------------------------------------------------
name's Profile: http://www.excelforum.com/member.php...o&userid=34115
View this thread: http://www.excelforum.com/showthread...hreadid=545146

array formula with constant
 

Assuming that A1:B4 contains the data, try...

=SUMPRODUCT(A1:A4,B1:B4)/SUM(A1:A4)

Hope this helps!

In article ,
name wrote:

I'm trying to use an array formula to calculate the weighted average.

Gross
A:10
B:20
C:30
D:1000
----------
Total:1060

Rates
A: 15%
B: 10%
C: 30%
D: 85%

How do I use an array forumula to calculate the weighted average rate
based on gross numbers? For example, (10/1060)*15%+(20/1060)*10% and
so on?

Any help is appreciated.

array formula with constant
 

Let's say your gross #'s are in A1:D1 and the % numbers are in A2:D2
I believe you want

=SUMPRODUCT(A1:D1,A2:D2)/sum((A1:D1)

"name" wrote:


I'm trying to use an array formula to calculate the weighted average.

Gross
A:10
B:20
C:30
D:1000
----------
Total:1060

Rates
A: 15%
B: 10%
C: 30%
D: 85%

How do I use an array forumula to calculate the weighted average rate
based on gross numbers? For example, (10/1060)*15%+(20/1060)*10% and
so on?

Any help is appreciated.


--
name
------------------------------------------------------------------------
name's Profile: http://www.excelforum.com/member.php...o&userid=34115
View this thread: http://www.excelforum.com/showthread...hreadid=545146