Forum Discussion
Solved
SumProduct for Weightd Average
Hello Experts, I am trying to use SumProduct to return the weighted average of various rates and amounts for companies. How can I use SumProduct to do this? I think I need an If statement in the...
The definition of the Rate range is not correct - it refers to =Confirmations!$D$34:$D$54 instead of to =Confirmations!$D$7:$D$54. If you change that, the formula will work.
For example:
=SUM(IF(Vendor="Co A",EUR*Weight))/SUMIF(Vendor,"Co A",Weight)
or
=SUMPRODUCT((Vendor="Co A")*EUR*Weight)/SUMIF(Vendor,"Co A",Weight)
for EUR, and
=SUM(IF(Vendor="Co A",Rate*Weight))/SUMIF(Vendor,"Co A",Weight)
or
=SUMPRODUCT((Vendor="Co A")*Rate*Weight)/SUMIF(Vendor,"Co A",Weight)
for Rate.
- Hi Hans, thank you. I tried the 2 formulas for RATE and each of the formulas returns a #N/A. I imagine I am doing something wrong but I am not sure what it could be?
The definition of the Rate range is not correct - it refers to =Confirmations!$D$34:$D$54 instead of to =Confirmations!$D$7:$D$54. If you change that, the formula will work.
- Hi Hans, yes that was it. I had a named range error. thank you very much!
