% Calculations for positive and negative

[AbrahamGluck]

[TABLE="class: grid, width: 700, align: center"]
<tbody>[TR]
[TD="align: center"]Symbol[/TD]
[TD="align: center"]Price now[/TD]
[TD="align: center"]Paid - Credit/Debit[/TD]
[TD="align: center"]Win/Loss %[/TD]
[TD="align: center"]Expectation %[/TD]
[TD="align: center"]Expectation $[/TD]
[/TR]
[TR]
[TD="align: center"]AAPL[/TD]
[TD="align: center"]-1.50[/TD]
[TD="align: center"]-3.00[/TD]
[TD="align: center"]WIN[/TD]
[TD="align: center"]50%[/TD]
[TD="align: center"]$1.50[/TD]
[/TR]
[TR]
[TD="align: center"]AAPL[/TD]
[TD="align: center"]+1.88[/TD]
[TD="align: center"]+1.50[/TD]
[TD="align: center"]WIN[/TD]
[TD="align: center"]25%[/TD]
[TD="align: center"]$0.375[/TD]
[/TR]
[TR]
[TD="align: center"]AAPL[/TD]
[TD="align: center"]+1.88[/TD]
[TD="align: center"]+1.50[/TD]
[TD="align: center"]25%[/TD]
[TD="align: center"]50%[/TD]
[TD="align: center"]$0.75[/TD]
[/TR]
[TR]
[TD="align: center"]AAPL[/TD]
[TD="align: center"]-6.00[/TD]
[TD="align: center"]-3.00[/TD]
[TD="align: center"]-100%[/TD]
[TD="align: center"]50%[/TD]
[TD="align: center"]$1.50[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]


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,

Last attempt:

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	2	3,01	-34%	25%	0.75
6	AAPL	-4	-3	-33%	125%	0.76
Sheet1

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

 

[AbrahamGluck]

Re: % Calculations for positive and negetive

Hi,

Last attempt:

	A	B	C	D	E	F

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Symbol[/TD]
[TD="align: center"]Price now[/TD]
[TD="align: center"]Paid - Credit/Debit[/TD]
[TD="align: center"]Win/Loss %[/TD]
[TD="align: center"]Expectation %[/TD]
[TD="align: center"]Expectation $[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]AAPL[/TD]
[TD="align: center"]-1,5[/TD]
[TD="align: center"]-3[/TD]
[TD="align: center"]Win[/TD]
[TD="align: center"]50%[/TD]
[TD="align: right"]1,5[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]AAPL[/TD]
[TD="align: center"]1,88[/TD]
[TD="align: center"]1,5[/TD]
[TD="align: center"]Win[/TD]
[TD="align: center"]25%[/TD]
[TD="align: right"]0,375[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]AAPL[/TD]
[TD="align: center"]1,88[/TD]
[TD="align: center"]1,5[/TD]
[TD="align: center"]25%[/TD]
[TD="align: center"]50%[/TD]
[TD="align: right"]0,75[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]AAPL[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3,01[/TD]
[TD="align: center"]-34%[/TD]
[TD="align: center"]25%[/TD]
[TD="align: right"]0.75[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]AAPL[/TD]
[TD="align: center"]-4[/TD]
[TD="align: center"]-3[/TD]
[TD="align: center"]-33%[/TD]
[TD="align: center"]125%[/TD]
[TD="align: right"]0.76[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=IF((B2-C2)>=F2,"Win",ABS((B2-C2)/C2)*SIGN(B2-C2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

That's great, just test it out on a few ways, and it works great!