How do I set up a weighted average that does not count cells that are empty?

[slim6y]

Hi all,

I am looking to get data from my table of unit costs by putting a weighted average. But after a unit has been moved, I empty a cell meaning I want that removed from the weighted average.

I have this calculation:

=IFERROR(SUMPRODUCT(Purchases_XYZ[Cost Per Unit],Purchases_XYZ[Cost])/SUM(Purchases_XYZ[Cost]),"")

This gives me the weighted average. But, if I remove a "[Cost Per Unit]" cell (because I have removed that unit completely) I want that row to be removed from the weighted average.

So I am looking for an "IF" statement perhaps. IF [Cost Per Unit] =< 0 then don't count that in the sumproduct.

How do I write that out?

TIA.

 

[slim6y]

Ok, solved!!!

=IFERROR(SUMPRODUCT(Purchases_XYZ[Cost Per Unit],--(Purchases_XYZ[Cost Per Unit]<>0),Purchases_XYZ[Cost])/SUMPRODUCT(Purchases_XYZ[Cost],--(Purchases_XYZ[Cost Per Unit]<>0)),"")