Weighted average but with negative values - % return on purchase and sales

[skghost]

I apologize in advance as I will try my best to explain my predicament. I am attempting to find a way to calculate net gain or loss from the purchase or sale of an item. However, by inputting negative values in to the items sold column, it is throwing off my numbers.

Initially I was using a weight sum average formula (this seemed to throw off my "cost basis") - "=SUMPRODUCT(A1:A3, B1:B3)/SUM(A1:A3)"

As you can see in the image Weighted average in column B above is where I would use the weighted sum average, but to get the desired % return in column G, weighted average should be at $20


Which area of my set up do I need to change to get the desired % return of my current holdings in column G? Thank you for any help!

 

[JamesonMH]

I apologize in advance as I will try my best to explain my predicament. I am attempting to find a way to calculate net gain or loss from the purchase or sale of an item. However, by inputting negative values in to the items sold column, it is throwing off my numbers.

Initially I was using a weight sum average formula (this seemed to throw off my "cost basis") - "=SUMPRODUCT(A1:A3, B1:B3)/SUM(A1:A3)"

As you can see in the image Weighted average in column B above is where I would use the weighted sum average, but to get the desired % return in column G, weighted average should be at $20


Which area of my set up do I need to change to get the desired % return of my current holdings in column G? Thank you for any help!

Welcome to the forum.

There is no image in your post as you noted and would be needed to understand your problem. You'll need to type an example in your post (insert rows/cols and data) or use an add-in like the HTML maker (search this site for this and other add-ins).

James