kumara_faith
Well-known Member
- Joined
- Aug 19, 2006
- Messages
- 955
- Office Version
- 365
Hi,
I have the following table:
1. Column A is the date
2. Column B is the product price.
3. Column C is the daily price difference.
4. Column D indicate the days where the user buys the product with 1 unit.
5. Column F is the aggregate capital used to buy the products. It is zerorised when the indication is to sell.
6. Column G is the aggregate units of the products purchased. It is zerorised when the indication is to sell.
I am trying to find a formula for the following:
In column E, I am trying to build a formula that will only sell if the profit is equal to or more than a certain percentage indicated in cell L2. Example, in cell B14, the price went to 25.6 and the profit is 13%. As such, column E will indicate to sell. Similar scenario when the price went to 26.8 in cell B17, the profit is 30% and column E will indicate to sell. Is there a formula that could be used for column E ?Appreciate all the help.
I have the following table:
| Book4.xlsx | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | |||
| 1 | Date | Price | % Difference | Buy | Sell | Buy Capital | Buy Units | Sell Amount | Profit | Profit % | Profit % | |||
| 2 | 5/9/1996 | 23.2 | 5% | |||||||||||
| 3 | 5/10/1996 | 24 | 3.45% | 0 | 0 | 0.00 | 0.00 | #DIV/0! | ||||||
| 4 | 5/13/1996 | 23.9 | -0.42% | 0 | 0 | 0.00 | 0.00 | #DIV/0! | ||||||
| 5 | 5/14/1996 | 23.6 | -1.26% | 23.6 | 24 | 1 | 0.00 | 0.00 | #DIV/0! | |||||
| 6 | 5/15/1996 | 23.2 | -1.69% | 23.2 | 47 | 2 | 23.20 | -0.40 | -2% | |||||
| 7 | 5/16/1996 | 22.6 | -2.59% | 22.6 | 69 | 3 | 45.20 | -1.60 | -3% | |||||
| 8 | 5/17/1996 | 22.8 | 0.88% | 69 | 3 | 68.40 | -1.00 | -1% | ||||||
| 9 | 5/20/1996 | 22.5 | -1.32% | 22.5 | 92 | 4 | 67.50 | -1.90 | -3% | |||||
| 10 | 5/21/1996 | 22.6 | 0.44% | 92 | 4 | 90.40 | -1.50 | -2% | ||||||
| 11 | 5/22/1996 | 22 | -2.65% | 22 | 114 | 5 | 88.00 | -3.90 | -4% | |||||
| 12 | 5/23/1996 | 22.1 | 0.45% | 114 | 5 | 110.50 | -3.40 | -3% | ||||||
| 13 | 5/24/1996 | 21.6 | -2.26% | 21.6 | 136 | 6 | 108.00 | -5.90 | -5% | |||||
| 14 | 5/28/1996 | 25.6 | 18.52% | 25.6 | 0 | 0 | 153.60 | 18.10 | 13% | |||||
| 15 | 5/29/1996 | 20.8 | -18.75% | 20.8 | 21 | 1 | 0.00 | 0.00 | #DIV/0! | |||||
| 16 | 5/30/1996 | 20.5 | -1.44% | 20.5 | 41 | 2 | 20.50 | -0.30 | -1% | |||||
| 17 | 5/31/1996 | 26.8 | 30.73% | 26.8 | 0 | 0 | 53.60 | 12.30 | 30% | |||||
Sheet2 | ||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C3:C17 | C3 | =(B3/B2)-1 |
| D3:D17 | D3 | =IF(C3 <= -1%, B3, "") |
| F3 | F3 | =IF(E3<>"", 0, IF(D3<>"", D3, 0)) |
| G3 | G3 | =IF(E3<>"", 0, IF(D3<>"", 1, 0)) |
| H3:H17 | H3 | =B3*G2 |
| I3:I17 | I3 | =H3-F2 |
| J3:J17 | J3 | =I3/F2 |
| F4:F17 | F4 | =IF(E4<>"", 0, IF(F3=0, IF(D4<>"", D4, 0), F3 + IF(D4<>"", D4, 0))) |
| G4:G17 | G4 | =IF(E4<>"", 0, IF(G3=0, IF(D4<>"", 1, 0), G3 + IF(D4<>"", 1, 0))) |
1. Column A is the date
2. Column B is the product price.
3. Column C is the daily price difference.
4. Column D indicate the days where the user buys the product with 1 unit.
5. Column F is the aggregate capital used to buy the products. It is zerorised when the indication is to sell.
6. Column G is the aggregate units of the products purchased. It is zerorised when the indication is to sell.
I am trying to find a formula for the following:
In column E, I am trying to build a formula that will only sell if the profit is equal to or more than a certain percentage indicated in cell L2. Example, in cell B14, the price went to 25.6 and the profit is 13%. As such, column E will indicate to sell. Similar scenario when the price went to 26.8 in cell B17, the profit is 30% and column E will indicate to sell. Is there a formula that could be used for column E ?Appreciate all the help.
