# Calculations based on segments in a table

1. ## Calculations based on segments in a table

I have attached a workbook which contains a sample trade history. Interspersed between trades are deposits and withdrawals. I want to sum up the profit/loss between these deposits/withdrawals and then obtain a % profit/loss based on the account balance at the time of the previous deposit/withdrawal.

Then I want to sum up the total of each of the above calculations on a monthly basis.

The attached workbook provides the desired outcome, along with clarifying comments.

2. ## Re: Calculations based on segments in a table

Hi, I have not the complete solution, but you can start with this:
to copy down from K4 and L4:
``Please Login or Register  to view this content.``
For the totalling of months you can copy this down from M4:
``Please Login or Register  to view this content.``
Cheers
Erwin

3. ## Re: Calculations based on segments in a table

K4
=IF((LEN(E4)<5)*((LEN(E5)>5)+(MONTH(B4)<>MONTH(B5))),SUM(INDEX(F\$3:F4,MATCH(2,1/((LEN(E\$2:E3)>5)+(MONTH(B\$2:B3)<>MONTH(B4))))):H4),"")

L4
=IF(K4="","",K4/LOOKUP(2,1/(LEN(E\$2:E3)>5),I\$2:I3))

M4
=IF(MONTH(B4)<>MONTH(B5),SUM(INDEX(L\$3:L4,MATCH(2,1/(MONTH(B\$2:B3)<>MONTH(B4)))):L4),"")

4. ## Re: Calculations based on segments in a table

Thanks! I appreciate your help. I had started a solution which involved lots of helper columns but your solution is much more elegant.

There are currently 1 users browsing this thread. (0 members and 1 guests)