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

sacajamo

New Member
Joined
May 9, 2007
Messages
18
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?
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Maybe this:

=(SUM(ABS(MIN(F7-C7,5)))*100*B7)-SUM(D7*B7)*100+E7
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
Can you provide some sample data then and the expected results?
Today was a good example:


ContractsAnchor StrikeDebitFeesClosing PriceNet 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.
 
Upvote 0
Mappe12
BCDEFGH
2ContractsAnchor StrikeDebitFeesClosing PriceNet Profit/Loss
3952102-42,645203,583935,362657,36
Tabelle2
Cell Formulas
RangeFormula
G3G3=(ABS(MIN(5,F3-C3))*100*B3)-(D3*B3)*100+E3
H3H3=MIN(5,ABS(F3-C3))*100*B3-(D3*B3)*100+E3
 
Upvote 0
Solution

Forum statistics

Threads
1,223,896
Messages
6,175,262
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top