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:
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.
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:
Excel Formula:
=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.