Calculation based on a condition

[Guest]

Day Investment Return Status Net Profit
1 132 1200
2 143 1300 Yes 1025
3 165 1500
4 187 1700
5 209 1900
6 231 2100 Yes 2333
7 264 2400
8 297 2700 Yes 5497

I have the above data in range A1:E10

I would like to do calculation based on condition. I am looking for formula
in range E2:E9 or a VBA to calculate the Net profit based on wherever there
is a Yes in column D.

Currently there are 8 records, this is just a sample data, actually I have
48 records in my file. There could be any number of "Yes" in the Status
column also it can be anywhere. There is no fixed row. What I want is,
whenever I put a Yes in the Status column, in the next cell, it should
calculate the net profit.

In this example, the first Yes is at D3 therefore, it should deduct the
total investment till day2 with the "Returns" in day2 and that should be the
net profit for day2. Once this is done, check for the next Yes. In this
example, it is at D7 therefore, it should now deduct the total investment
from day3 to day6 with the "Returns" in day6 and add the first net profit.
Similarly, check for the next Yes. In this example, it is at D9 there fore,
it should now deduct the total investment from day7 to day8 with "Returns" in
day8 and add the first two net profits.

I have 48 records in my file and the formula or vba should give me
flexibility to put Yes anywhere I want and any number of times and calculate
the correct net profit for the day.

 

[Domenic]

Try...

E2, copied down:

=IF(D2="Yes",C2-SUM(INDEX($B$2:B2,MATCH(REPT("z",255),$D$11)):B2)+SUM($
E$1:E1),"")

Hope this helps!

 

[Bob Phillips]

Here is a formula that you can put in D2 and copy down

=($C2="Yes")*($B2-SUM(INDIRECT("A"&MAX(IF($C$1:$C1="Yes",ROW($C$1:$C1),0)+1)
&":A"&ROW()))+SUM(D$11))

it is an array formula, so commit with Ctrl-Shift-Enter.

One problem, the non calculated cells in column D show 0 not blank, you
could hide them with conditional formatting.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Wrong columns, try

=($D2="Yes")*($C2-SUM(INDIRECT("B"&MAX(IF($D$1:$D1="Yes",ROW($D$1:$D1),0)+1)
&":B"&ROW()))+SUM(E$1:E1))

still array formula


--

HTH

RP
(remove nothere from the email address if mailing direct)