| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Formula - calculate only if net unit is zero
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Formula - calculate only if net unit is zero
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 ^-^" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Formula [Note: The "net profit" here is not accumulating]
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" ¦b¶l¥ó ¤¤¼¶¼g... 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 ^-^" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Formula - calculate only if net unit is zero [modified]
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Formula - calculate only if net unit is zero
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. "Marcelo" ¦b¶l¥ó ¤¤¼¶¼g... 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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Formula [Note: The "net profit" here is not accumulating]
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 ^-^" wrote in message ... 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" ¦b¶l¥ó ¤¤¼¶¼g... 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 ^-^" wrote in message ... 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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Formula [Note: The "net profit" here is not accumulating]
¥»¤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" ¦b¶l¥ó ¤¤¼¶¼g... 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 ^-^" wrote in message ... 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" ¦b¶l¥ó ¤¤¼¶¼g... 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 ^-^" wrote in message ... 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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Formula [Note: The "net profit" here is not accumulating]
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 ^-^" wrote in message ... ¥»¤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" ¦b¶l¥ó ¤¤¼¶¼g... 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 ^-^" wrote in message ... 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" ¦b¶l¥ó ¤¤¼¶¼g... 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 ^-^" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| conditional formatting: problem entering EOMONTH formula... | Excel Discussion (Misc queries) | |||
| conditional formatting: problem entering EOMONTH formula... | Excel Discussion (Misc queries) | |||
| conditional formatting: problem entering EOMONTH formula... | Excel Discussion (Misc queries) | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| Formula Dependant Conditional Formatting | Excel Discussion (Misc queries) | |||
Linear Mode
