Conditional Formula - calculate only if net unit is zero

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.

Hi,

try to use if

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

hth
regards from Brazil
Marcelo




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

>
> 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.
>
>
>

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 ^-^" <[email protected]> wrote in message
news:[email protected]...
>
> 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.
>
>

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.


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


"Bob Phillips" <[email protected]> 秎ン
news:[email protected] い级糶...
> 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 ^-^" <[email protected]> wrote in message
> news:[email protected]...
> >
> > 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.
> >
> >
>
>

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.



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

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

セ獶盽Τ**. Τぃ讽ぇ矪, 辨醚ぃ界タ!!
After all, the above are merely my little opinion/idea.
Since my ability is limited, I could be wrong.
"Marcelo" <[email protected]> 秎ン
news:[email protected] い级糶...
> Hi,
>
> try to use if
>
> =if(net unit=0,net profit,false)
>
> hth
> regards from Brazil
> Marcelo
>
>
>
>
> "0-0 Wai Wai ^-^" escreveu:
>
> >
> > 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.
> >
> >
> >

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 ^-^" <[email protected]> wrote in message
news:%[email protected]...
>
> 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.
>
>
> --
> Additional info about my computer:
> - Office XP
> - Windows XP Pro
>
>
> "Bob Phillips" <[email protected]> 秎ン
> news:[email protected] い级糶...
> > 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 ^-^" <[email protected]> wrote in message
> > news:[email protected]...
> > >
> > > 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.
> > >
> > >
> >
> >
>
>

セ獶盽Τ**. Τぃ讽ぇ矪, 辨醚ぃ界タ!!
After all, the above are merely my little opinion/idea.
Since my ability is limited, I could be wrong.
"Bob Phillips" <[email protected]> 秎ン
news:[email protected] い级糶...
> 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.


> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "0-0 Wai Wai ^-^" <[email protected]> wrote in message
> news:%[email protected]...
> >
> > 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.
> >
> >
> > --
> > Additional info about my computer:
> > - Office XP
> > - Windows XP Pro
> >
> >
> > "Bob Phillips" <[email protected]> 秎ン
> > news:[email protected] い级糶...
> > > 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 ^-^" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >
> > > > 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.
> > > >
> > > >
> > >
> > >
> >
> >
>
>

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)

"0-0 Wai Wai ^-^" <[email protected]> wrote in message
news:%[email protected]...
>
>
>
> セ獶盽Τ**. Τぃ讽ぇ矪, 辨醚ぃ界タ!!
> After all, the above are merely my little opinion/idea.
> Since my ability is limited, I could be wrong.
> "Bob Phillips" <[email protected]> 秎ン
> news:[email protected] い级糶...
> > 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.
>
>
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "0-0 Wai Wai ^-^" <[email protected]> wrote in message
> > news:%[email protected]...
> > >
> > > 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.
> > >
> > >
> > > --
> > > Additional info about my computer:
> > > - Office XP
> > > - Windows XP Pro
> > >
> > >
> > > "Bob Phillips" <[email protected]> 秎ン
> > > news:[email protected] い级糶...
> > > > 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 ^-^" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > >
> > > > > 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.
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>