|
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. |
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. |
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. |
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" 秎ン い级糶... 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. |
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 |
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 セ獶盽Τ**. Τぃ讽ぇ矪, 辨醚ぃ界タ!! After all, the above are merely my little opinion/idea. Since my ability is limited, I could be wrong. "Marcelo" 秎ン い级糶... 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. |
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" 秎ン い级糶... 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. |
Conditional Formula [Note: The "net profit" here is not accumulating]
セ獶盽Τ**. Τぃ讽ぇ矪, 辨醚ぃ界タ!! 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)) 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" 秎ン い级糶... 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. |
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 ... セ獶盽Τ**. Τぃ讽ぇ矪, 辨醚ぃ界タ!! 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)) 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" 秎ン い级糶... 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. |
| All times are GMT +1. The time now is 06:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com