Day Investment Total Investment Return Net Profit Status Net Profit
1 132 132 1200 1068
2 143 275 1300 1025
3 165 440 1500 1060 Yes 1060
4 187 627 1700 1073
5 209 836 1900 1064
6 231 1067 2100 1033
7 264 1331 2400 1069 Yes 4666
8 297 1628 2700 1072


This is what I have in range A1:G9

Range F2:F9 has a validation where the user can select either Yes or No. I
want to calculate the second net profit (column G) based on the status
(column F)

For example, If I select Yes in cell F4, the net profit is going to be the
same as in E4 and the total investment till the 3rd day is nullified. Now if
I select Yes in cell F8, it should take the total investment from C5 and
ignore C2:C4 as it was nullified above and the net profit should be 4666
instead of 1069.

Lets say if I select Yes in F2 and F5 then the second Net profit for G2 will
be 1068 and G5 will be 2228

What formula should I put in range G2:G9?

Also Do I really want to have the first Net Profit column?

For example:

If we have Yes in cell F6 and F9, then G6 should show the value present in
E6 (1064) and G9 should autocalculate the next netprofit i.e., That day's net
profit (1072) - sum of investment from next day of first YES (231+264+297) +
last net profit (1064) [=(D9-SUM(B7:B9)+G6)]

I would put Yes anywhere in F column once, twice or more than two times the
formula or VBA should calculate all Net profits in column G