|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
June 27th, 2006, 11:42 AM
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
June 27th, 2006, 11:59 AM
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
June 27th, 2006, 01:24 PM
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
June 27th, 2006, 01:57 PM
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
June 27th, 2006, 02:01 PM
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
June 27th, 2006, 02:05 PM
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
June 27th, 2006, 02:14 PM
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(D20,"",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
June 27th, 2006, 07:04 PM
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(D20,"",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
June 28th, 2006, 11:07 AM
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(D20,"",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.
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| conditional formatting: problem entering EOMONTH formula... | Jonathan Cooper | General Discussion | 0 | February 6th, 2006 09:34 PM |
| Formula Problem - interrupted by #VALUE! in other cells!? | Ted | Worksheet Functions | 17 | November 25th, 2005 05:18 PM |
| Formula checking multiple worksheets | sonic-the-mouse | Worksheet Functions | 11 | June 6th, 2005 06:37 PM |
| Cannot Calculate the Formula | Ellis Yu | Setting up and Configuration | 1 | April 6th, 2004 07:39 AM |
Linear Mode
