I have figured I have done something wrong because I can't seem to get the statement to work.

So, I have this:

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

This gives me a weighted average and I am happy with this.

But what if I have removed some of these purchases (I may have sold some stock or similar). So in this case, the "cost" cell is deleted and left blank. I am not even sure I need the "IFERROR" anymore either. Before I was just doing a straight average but I really need a weighted average.

So if the Cost cell = 0 then I don't want to count that in the weighted average. But if the Cost cell > 0 then count it in the weighted average.

Unless you can think of another way around this where I can leave the cost in and if I move the stock around, it won't be counted toward the weighted average. Because the way I am doing it right now is clumsy. I am deleting the cell and just adding a comment with the price purchased.

Notes: I am using Excel from Office 2019

CodePudding user response：

You need to add a conditional check that meets your needs and convert it from true false to 1 or 0. Use the formula below and adjust ranges to suit your needs.

=SUMPRODUCT(--(B2:B6>0),B2:B6,A2:A6)/SUMPRODUCT(--(B2:B6>0),B2:B6)