array formula with constant

  • Thread starter Thread starter name
  • Start date Start date
N

name

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.
 
Assuming that A1:B4 contains the data, try...

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

Hope this helps!
 
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)
 
Back
Top