Percentage Profit and Loss - Not so simple

I have a list of items with buy prices ranging from \$0.5 to \$3000 and quantities can be bought from 0.5 a unit to thousands of units. Here is an

What's wrong with the data?

P&L Percentage This is the profit and loss percentage, the P & L Column is showing a profit however P & L Column is showing a loss.

The Formula used to Calculate P & L `=(E3/D3-C3/B3)*D3`

The Formula used to Calculate P&L Percentage - A note this is formula used to calculate total percentage NOT row wise `=(SUM(Sell_Costs_Proceeds)/Sum(Sell_Base_Qty)-sum(Buy_Costs_Proceeds)/sum(Buy_Base_Qty))/(sum(Buy_Costs_Proceeds)/sum(Buy_Base_Qty))`

The percentage should obviously be in postive.

Some key points:

1. Buy and Sell Numbers can be different so profit is accordingly calculated.
2. quantity has a huge disparity some quantities are less in 1 (0.2) and some thousands (17237). Cost is the same some costs are very high and some low.

Here is the link for the sheet

• Celestial Steed - How can you sell more (1808) than you bought (1791)? Aug 9, 2020 at 12:37
• let's say previously bought
– Uma
Aug 12, 2020 at 18:42
• "The percentage should obviously be in positive." What do you think is the "correct" percentage value? Aug 13, 2020 at 3:14

You believe that the Total "P&L%" generated by your formula is incorrect. This is true. The formula uses sum totals of various fields, but the result is meaningless. It needs to calculate the aggregate result on a unit-by-unit basis.

The correct P&L% is 16.69%.

Try this formula in cell G10: `=F10/ARRAYFORMULA(SUMPRODUCT(C3:C9 / B3:B9,D3:D9))`

The logic is:

• Total profit (\$552.778): `Cell F10`.
• Total cost of sales (\$3312.67):
• `ARRAYFORMULA(SUMPRODUCT(C3:C9 / B3:B9,D3:D9))`
• "Buy Cost Proceeds" (Column C) divided by respective "Buy Base Quantity" (Column B) = cost per unit.
• Cost per Unit multiplied by "Sell Base Quantity" (Column D) = Total cost of units sold.
• Total profit/Total cost of units sold = \$552.778/\$3312.67 = 16.69%

The screenshot shows a longhand proof of the result. It also shows an analysis of the balances in your formula.

• thanks @Tedinoz for your help. This solves it
– Uma
Aug 14, 2020 at 21:13