Trying to add an "IF, THEN" to an existing formula

[sacajamo]

Pretty sure I am going about this the wrong way so need some help. I have a spreadsheet I used to track my stock options trades. There are some trades that I reach max profit on if it moves $5 away from my "anchor strike". So if it goes up $5.01 or $5000, I make the same amount. I am having trouble figuring out how to "cap" the max profit within the formula. The formula needs to read something like, "If F3-C3 is > $5, then $5". That part needs to be added to the existing formula in the cell which is currently:

=(SUM(ABS(F7-C7))*100*B7)-SUM(D7*B7)*100+E7

Make sense? How can I add it?

 

[dreid1011]

Maybe this:

=(SUM(ABS(MIN(F7-C7,5)))*100*B7)-SUM(D7*B7)*100+E7

 

[shift-del]

That's MIN(5,F3-C3).

 

[sacajamo]

Hmmm...I think we are on the right track but something is still off. It is still calculating sums that are greater than 5 in the formula. This is how it is now entered but still returning an incorrect value where sum of F3-C3 is greater than 5...

=(SUM(ABS(MIN(5,F3-C3)))*100*B3)-SUM(D3*B3)*100+E3

 

[dreid1011]

Hmmm...I think we are on the right track but something is still off. It is still calculating sums that are greater than 5 in the formula. This is how it is now entered but still returning an incorrect value where sum of F3-C3 is greater than 5...

=(SUM(ABS(MIN(5,F3-C3)))*100*B3)-SUM(D3*B3)*100+E3

Can you provide some sample data then and the expected results?

 

[sacajamo]

Can you provide some sample data then and the expected results?

Today was a good example:

Contracts

Anchor Strike

Debit

Fees

Closing Price

Net Profit/Loss

9​

5210​

$2.00​

-$42.64​

5203.58​

$3,935.36​

In the Net Profit/Loss Column, I want to get a result of $2,657.36. Absolute value of Closing Price - Anchor strike X # of contracts minus Debit X # of contracts + fees. But closing price - anchor strike needs to be capped at 5. Since it is greater than 5 (5203.58-5210 = $6.42), it is showing more profit than I actually made. Let me know if this make sense.

 

[shift-del]

Mappe12
	B	C	D	E	F	G	H
2	Contracts	Anchor Strike	Debit	Fees	Closing Price	Net Profit/Loss
3	9	5210	2	-42,64	5203,58	3935,36	2657,36
Tabelle2

Cell Formulas
Range	Formula
G3	G3	=(ABS(MIN(5,F3-C3))*100*B3)-(D3*B3)*100+E3
H3	H3	=MIN(5,ABS(F3-C3))*100*B3-(D3*B3)*100+E3

 

[sacajamo]

Thanks, Shift Del! Perfect!