I'm trying to calculate the weighted average of stock prices but the problem is that some values are negative (where some but not all stocks were sold) and I can't figure out how to make it work.

Here is how I manged to do it with only positive values and it works fine:

Only positives

But if I try with some negative values added in I get an error: enter image description here

Any advice would be appreciated. Thank you!

  • Welcome to Web Applications Stack Exchange. Sep 16 at 16:37
  • I suggest that you show the expected returns given the negative values in Col H, rather than simply showing #VALUE errors. Then explain how you arrived at those expected values.
    – Erik Tyler
    Sep 17 at 4:30

1 Answer 1


Use sumproduct() to calculate the weighted sum, and divide that by the total quantity, like this:

  filter(H$2:H, G$2:G = J2), 
  filter(I$2:I, G$2:G = J2) 
) / sum(filter(H$2:H, G$2:G = J2))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.