sumproduct function
 

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

--
Message posted via http://www.officekb.com

=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<""))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Andreea Moyes via OfficeKB.com" wrote in message
...
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

--
Message posted via http://www.officekb.com

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
<snip

Ken Wright wrote:
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