Calculation based on a condition
 

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.

Try...

E2, copied down:

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

Hope this helps!

In article ,
"mac_see" wrote:

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.

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:$C 1),0)+1)
&":A"&ROW()))+SUM(D$1:D1))

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)


"mac_see" wrote in message
...
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.

Wrong columns, try

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

still array formula


--

HTH

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


"Bob Phillips" wrote in message
...
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:$C 1),0)+1)
&":A"&ROW()))+SUM(D$1:D1))

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)


"mac_see" wrote in message
...
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.