sumproduct function

[Andreea Moyes via OfficeKB.com]

Hi,

I have a function I'm trying to write and I don't know what I'm doing wrong.

I am trying to calculate a weighted average, but some of my metrics are
missing, so when they're missing, I'd like the average to adjust
automatically to include just the metrics I have numbers for.

Example

Weights 20 30 30 20 Function
Metric 10 10 10 10 (10*20+10*30+10*30+20*30)/100
Metric 10 5 (10*20+5*30)/50
Metric 10 10 (10*20+10*20)/40

Any idea how to achieve this?

Thanks for your help!!!!
Andreea

 

[Ken Wright]

=SUMPRODUCT(--$B$1:$E$1,--B2:E2)/SUMPRODUCT(--$B$1:$E$1,--ISNUMBER(B2:E2))

or

=SUMPRODUCT(--$B$1:$E$1,--B2:E2)/SUMPRODUCT(--$B$1:$E$1,--(B2:E2<>""))

 

[Ken Wright]

By the way I can't get any formula to equal your first example

 

[Aladin Akyurek]

By the way I can't get any formula to equal your first example

Ken,

I guess the OP has a specification error there.

BTW, the formula can be a bit less expensive with SumIf:

=SUMPRODUCT($B$1:$E$1,B2:E2)/SUMIF(B2:E2,"<>",$B$1:$E$1)

Aladin