CODEWITHSUNDEEP
excelexcel-formulaexcel-2010forex
Brian White
Brian White

Reputation: 39

Separate Profit and Loss Numbers From One Column

I have imported data from an outside source into Excel. The data comes as "Profit" in one single column. However, it contains both profit and loss numbers. In order to provide a true analysis, I must be able to separate the data and recognize which cells are profit and which are losses. Below are screenshots of two different sheets of what I have developed so far, but hit a road block.

enter image description here enter image description here

I am trying to take the profit/loss in Column R from the top picture, and calculate these on spreadsheet to (4 Hour) results for G8 & I8 on the bottom picture. If I can do this, all other calculations will be successful. I have not been able to get this to operate properly. Any answers would be appreciated.

Upvotes: 0

Views: 1767

Answers (1)

Robin Mackenzie
Robin Mackenzie

Reputation: 19289

My assumptions are that a) cell formatting has been applied to remove the - from negative numbers and colour them red. And b) loss is negative and profit is positive.

Based on the fixed ranges of the example supplied, profit is calculated by:

=SUMIF(B2:B11,">=0",B2:B11)

And loss is calculated by:

=SUMIF(B2:B11,"<=0",B2:B11)

Test workings:

enter image description here

Upvotes: 1

Related Questions