Conditional Formula - calculate only if net unit is zero

[0-0 Wai Wai ^-^]

Here's the table for one game

Date-----Buy(Unit)-----Sell(Unit)-----Net Unit-----Price($)-----Net
Profit/Loss($)
xx/xx----2----------------##--------------2--------------125---------- ##
xx/xx----##---------------1---------------1--------------150---------- ##
xx/xx----##---------------1---------------0--------------165---------- +65
xx/xx----##---------------4--------------(4)-------------170--------- ##
xx/xx----2----------------##-------------(2)-------------165---------- ##
xx/xx----##---------------1--------------(3)-------------180---------- ##
xx/xx----3----------------##--------------0--------------180---------- (10)

Number in bracket means negative.
## means empty cell.

How can I type a formula, so that every time there's no stock left (ie zero net
unit), it will calculate the net profit once.
Any workaround is also appreciated.
Thank you.

 

[Guest]

Hi,

try to use if

=if(net unit=0,net profit,false)

hth
regards from Brazil
Marcelo




"0-0 Wai Wai ^-^" escreveu:

 

[Bob Phillips]

I get 55 as the final profit loss not (10), but try this in F2, and copy
down

=IF(D2=0,SUMPRODUCT($C$2:C2,$E$2:E2)-SUMPRODUCT($B$2:B2,$E$2:E2),"")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[0-0 Wai Wai ^-^]

Sorry! I forget to say.
The "net profit" here is not accumulating.

xx/xx----2----------------##--------------2--------------125---------- ##
xx/xx----##---------------1---------------1--------------150---------- ##
xx/xx----##---------------1---------------0--------------165---------- +65
Only the above entries is counted for the calculation of net profit/loss, ie 65.


xx/xx----##---------------4--------------(4)-------------170--------- ##
xx/xx----2----------------##-------------(2)-------------165---------- ##
xx/xx----##---------------1--------------(3)-------------180---------- ##
xx/xx----3----------------##--------------0--------------180---------- (10)
Only the above entries is counted for the calculation of net profit/loss, ie
(10).

Each of my tables is flexible. They vary in number of entries.
How can I tell the formula to group the entries and calculate each "net
profit/loss" separately?

Thank you.

 

[0-0 Wai Wai ^-^]

Here's the table for one game

Date-----Buy(Unit)-----Sell(Unit)-----Net Unit-----Price($)-----Net
Profit/Loss($)
xx/xx----2----------------##--------------2--------------125---------- ##
xx/xx----##---------------1---------------1--------------150---------- ##
xx/xx----##---------------1---------------0--------------165---------- +65
xx/xx----##---------------4--------------(4)-------------170--------- ##
xx/xx----2----------------##-------------(2)-------------165---------- ##
xx/xx----##---------------1--------------(3)-------------180---------- ##
xx/xx----3----------------##--------------0--------------180---------- (10)
.... ...
.... ...

Number in bracket means negative.
## means empty cell.


Group A:
xx/xx----2----------------##--------------2--------------125---------- ##
xx/xx----##---------------1---------------1--------------150---------- ##
xx/xx----##---------------1---------------0--------------165---------- +65
Only the above entries is counted for the calculation of net profit/loss, ie 65.

Group B:
xx/xx----##---------------4--------------(4)-------------170--------- ##
xx/xx----2----------------##-------------(2)-------------165---------- ##
xx/xx----##---------------1--------------(3)-------------180---------- ##
xx/xx----3----------------##--------------0--------------180---------- (10)
Only the above entries is counted for the calculation of net profit/loss, ie
(10).

Group C, D, E, F... ...

Each of my tables is flexible. They vary in number of entries.
How can I tell the formula to group the entries and calculate each "net
profit/loss" separately?

The "net profit" is calculated every time when there's no stock left (ie zero
net
unit). The "net profit" does not accumulate!

Any workaround is also appreciated.
Thank you.

 

[0-0 Wai Wai ^-^]

Sorry, I haven't clarified enough.

It is calculated each time when the stock falls to zero (ie net unit = 0).
However "net profit" does not accumulate!

xx/xx----2----------------##--------------2--------------125---------- ##
xx/xx----##---------------1---------------1--------------150---------- ##
xx/xx----##---------------1---------------0--------------165---------- +65
Only the above entries is counted for the calculation of net profit/loss, ie 65.


xx/xx----##---------------4--------------(4)-------------170--------- ##
xx/xx----2----------------##-------------(2)-------------165---------- ##
xx/xx----##---------------1--------------(3)-------------180---------- ##
xx/xx----3----------------##--------------0--------------180---------- (10)
Only the above entries is counted for the calculation of net profit/loss, ie
(10).

There're many of them.
How can I tell the forumla to group them and calculate accordingly.


--
Additional info about my computer:
- Office XP
- Windows XP Pro

¥»¤Hªº¯à¤O«D±`¦³­­. ¦p¦³¤£·í¤§³B, ±æÃѪ̤£§[½ç¥¿!!
After all, the above are merely my little opinion/idea.
Since my ability is limited, I could be wrong.

 

[Bob Phillips]

Try this adaptation then, again in F2 and copy down

=IF(D2<>0,"",SUMPRODUCT($C$2:C2,$E$2:E2)-SUMPRODUCT($B$2:B2,$E$2:E2)-SUM($F$
1:F1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[0-0 Wai Wai ^-^]

¥»¤Hªº¯à¤O«D±`¦³­­. ¦p¦³¤£·í¤§³B, ±æÃѪ̤£§[½ç¥¿!!
After all, the above are merely my little opinion/idea.
Since my ability is limited, I could be wrong.

Try this adaptation then, again in F2 and copy down

=IF(D2<>0,"",SUMPRODUCT($C$2:C2,$E$2:E2)-SUMPRODUCT($B$2:B2,$E$2:E2)-SUM($F$
1:F1))

Thanks for your formula.
Still I have to modify the cell references of each formula manually.
I would like to create one global formula which can apply to all instances.

 

[Bob Phillips]

Why do you. I tested it and I didn't have to.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

¥»¤Hªº¯à¤O«D±`¦³­­. ¦p¦³¤£·í¤§³B, ±æÃѪ̤£§[½ç¥¿!!
After all, the above are merely my little opinion/idea.
Since my ability is limited, I could be wrong.

Try this adaptation then, again in F2 and copy down

1:F1))

Thanks for your formula.
Still I have to modify the cell references of each formula manually.
I would like to create one global formula which can apply to all instances.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

profit/loss,
ie 65. profit/loss,
ie xx/xx----2----------------##--------------2--------------125----------
## xx/xx----##---------------1---------------1--------------150----------
## xx/xx----##---------------1---------------0--------------165----------
+65 xx/xx----##---------------4--------------(4)-------------170---------
## xx/xx----2----------------##-------------(2)-------------165----------
## xx/xx----##---------------1--------------(3)-------------180----------
##