% Calculations for positive and negative

[AbrahamGluck]

Symbol	Price now	Paid - Credit/Debit	Win/Loss %	Expectation %	Expectation $
AAPL	-1.50	-3.00	WIN	50%	$1.50
AAPL	+1.88	+1.50	WIN	25%	$0.375
AAPL	+1.88	+1.50	25%	50%	$0.75
AAPL	-6.00	-3.00	-100%	50%	$1.50

<tbody>
</tbody>

I have this Table above I want help for column D and F
Column A is the product,
Column B is the Price as of now,
Column C is Price I paid or received for the sale,
Column E is my % expectation to return on the capitol on my paid price, Column F is the same with the $ amount
Column D is Results after calculating if Column B (price now) met the Expectation (column E) after checking Column C what I paid for it at the first place, and the formula needs to work for positive and negative as you can see the difference in the table
if the excitation is as expected the return should be WIN if anything other the return should be a % amount if its profit is a positive % or negative for loss -%

 

[jorismoerings]

Re: % Calculations for positive and negetive

Hi,

check this:

Book1
	A	B	C	D	E	F	G
1	Symbol	Price now	Paid - Credit/Debit	Win/Loss %	Expectation %	Expectation $
2	AAPL	-1,50	-3,00	WIN	50%	1,50	Win
3	AAPL	1,88	1,50	WIN	25%	0,38	Win
4	AAPL	1,88	1,50	25%	50%	0,75	25%
5	AAPL	-6,00	-3,00	-100%	50%	1,50	-100%
Sheet1

Cell Formulas
Range	Formula
G2	=IF((B2-C2)>=F2,"Win",IF(B2>C2,((B2-C2)/C2),((C2-B2)/C2)))

 

[AbrahamGluck]

Re: % Calculations for positive and negetive

Hi,

check this:

	A	B	C	D	E	F	G
1	Symbol	Price now	Paid - Credit/Debit	Win/Loss %	Expectation %	Expectation $
2	AAPL	-1,50	-3,00	WIN	50%	1,50	Win
3	AAPL	1,88	1,50	WIN	25%	0,38	Win
4	AAPL	1,88	1,50	25%	50%	0,75	25%
5	AAPL	-6,00	-3,00	-100%	50%	1,50	-100%

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula G2 =IF((B2-C2)>=F2,"Win",IF(B2>C2,((B2-C2)/C2),((C2-B2)/C2))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thanks jorismoerings! so much that sounds great?
The F column has not a formula now, can you do that as well? in order the formula you provide me should work.

 

[jorismoerings]

Re: % Calculations for positive and negetive

Like this:

Book1
	A	B	C	D	E	F
1	Symbol	Price now	Paid - Credit/Debit	Win/Loss %	Expectation %	Expectation $
2	AAPL	-1,5	-3	Win	50%	1,5
3	AAPL	1,88	1,5	Win	25%	0,375
4	AAPL	1,88	1,5	25%	50%	0,75
5	AAPL	-6	-3	-100%	50%	1,5
Sheet1

Cell Formulas
Range	Formula
D2	=IF((B2-C2)>=F2,"Win",IF(B2>C2,((B2-C2)/C2),((C2-B2)/C2)))
F2	=ABS(E2*C2)

 

[AbrahamGluck]

Re: % Calculations for positive and negetive

Like this:

	A	B	C	D	E	F
1	Symbol	Price now	Paid - Credit/Debit	Win/Loss %	Expectation %	Expectation $
2	AAPL	-1,5	-3	Win	50%	1,5
3	AAPL	1,88	1,5	Win	25%	0,375
4	AAPL	1,88	1,5	25%	50%	0,75
5	AAPL	-6	-3	-100%	50%	1,5

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D2 =IF((B2-C2)>=F2,"Win",IF(B2>C2,((B2-C2)/C2),((C2-B2)/C2))) F2 =ABS(E2*C2) <tbody> </tbody>

<tbody>
</tbody>

Thanks jorismoerings! This is really great works perfectly, and will help me a lot.

Thank you,
Abraham

 

[jorismoerings]

Thanks for the feedback. Glad i could help.

 

[AbrahamGluck]

Re: % Calculations for positive and negetive

Like this:

	A	B	C	D	E	F
1	Symbol	Price now	Paid - Credit/Debit	Win/Loss %	Expectation %	Expectation $
2	AAPL	-1,5	-3	Win	50%	1,5
3	AAPL	1,88	1,5	Win	25%	0,375
4	AAPL	1,88	1,5	25%	50%	0,75
5	AAPL	-8.00	-15.00	-46.67	50%	7,5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D2 =IF((B2-C2)>=F2,"Win",IF(B2>C2,((B2-C2)/C2),((C2-B2)/C2))) F2 =ABS(E2*C2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Hi jorismoerings

I have start using this formula so I realized one problem, look on row 5 I pleased information the formula will return, in this case I want D5 to be a positive percentage return (instead of -46.67 should be positive) since C5 was much more negative than B5 so it's a win.

 

[jorismoerings]

Re: % Calculations for positive and negetive

Hi,

Change the formula to this:
=IF((B2-C2)>=F2,"Win",IF(B2>C2,(ABS(B2-C2)/ABS(C2)),((C2-B2)/C2)))

 

[AbrahamGluck]

Re: % Calculations for positive and negetive

Hi,

Change the formula to this:
=IF((B2-C2)>=F2,"Win",IF(B2>C2,(ABS(B2-C2)/ABS(C2)),((C2-B2)/C2)))

Thanks much jorismoerings

 

[AbrahamGluck]

Re: % Calculations for positive and negetive

Hi,

Change the formula to this:
=IF((B2-C2)>=F2,"Win",IF(B2>C2,(ABS(B2-C2)/ABS(C2)),((C2-B2)/C2)))

Now I have an opposite problem, the positive numbers will not shown a negative percentage number, if the positive number was gone lower than I paid for it at the first place, will shown positive no matter if has gone higher or lower.
as you can see on row 5 I paste in the info.

	A	B	C	D	E	F
1	Symbol	Price now	Paid - Credit/Debit	Win/Loss %	Expectation %	Expectation $
2	AAPL	-1,5	-3	Win	50%	1,5
3	AAPL	1,88	1,5	Win	25%	0,375
4	AAPL	1,88	1,5	25%	50%	0,75
5	AAPL	2.00	3.01	33.55%	25%	0.75

<tbody>
</tbody>