Calculate Profits

Greetings,

I need help doing a formula that will go through all the sales in this spreadsheet and find the difference. Sounds simple...until you realize the requirements for successful automation and reliable calculations finding values in the spreadsheet that can be separated by and unknown number of rows. I cannot for the life of me figure out how to target specific values in cells to find the differences. The formula must be required to click and drag down all the way through the spreadsheet. See attachment for where I'm currently at with it.

Row 70, column D, specifies a sale. Column K calculates the current balance. Column M needs to be where I find out the "take profit" (or Gains/Losses) of each transaction.

The answer for row 70 should be $196.80 (its the first sale ever, so technically 196.80 - 0 = 196.80). Then after that, the next sale needs to be 202.73 - 196.80 = $5.93. Between these 2 sales, 1 row of purchases were made. Between sales 2 and 3 (cells K73 and K77 respectively), 3 rows of purchases were made.

In this WIP, column M shows intuitively what our brains would do to calculate the numbers with paper and pencil. Column N is dumb stuff I've tried to make progress on obtaining the result, and is most likely nowhere close to the path I should be taking to write an advanced formula to pick out all the sales in the sheet.

Let me know if any more clarity is needed.

Thank you all for taking the time to help assist me.

Welcome to the forum.

Not sure I get it, but maybe you try this array formula in M6 and confirm with Ctrl+Shift+Enter instead of just Enter. Then copy down:

Formula:

Please Login or Register  to view this content.

Good luck!

Alternative with non array formula:

Please Login or Register  to view this content.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.