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

Sheets Calculates Pofit and Loss

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)? – Tedinoz Aug 9 '20 at 12:37
  • let's say previously bought – Uma Aug 12 '20 at 18:42
  • "The percentage should obviously be in positive." What do you think is the "correct" percentage value? – Tedinoz Aug 13 '20 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):
    • "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.

Screenshot with proof

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

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.