+ Reply to Thread
Results 1 to 6 of 6

If Statement with three criteria

  1. #1
    Registered User
    Join Date
    10-05-2019
    Location
    Hesperia, California
    MS-Off Ver
    2016
    Posts
    3

    If Statement with three criteria

    The attached spreadsheet is showing both a win and a loss for trades which were opened as a Credit and as a Debit.
    I am trying to change the formula in a cell based on whether another cell is reading the word “Credit” or “Debit”. In addition, I would like to look at another cell and if that cell is empty, and if so, then don’t apply the formula at all.
    I have the three formulas, which all work independently, but when I put them all on the same line, in the same cell, I get an error message that reads . . . “There is a problem with this formula. Not trying to type of formula? When the 1st character is equal or - Excel thinks it’s a formula”
    I need to know if I have a Profit or Loss in Row R of the attached spreadsheet.
    • If I sell an Option trade to open a contract, there is the word “Credit” in column D.
    • If I buy an Option trade to open a contract, there is the word “Debit” in column D.
    To determine profit or loss, this involves six fields
    1. D = Opening a trade with either a Debit (Buy to Open), or Credit (Sell to Open).
    2. F = Amount of money paid (Debit) or money received (Credit) to Open a trade, shown as the price of an individual share.
    a. (Note: Price paid is for an individual share, so price x 100 = cost of ONE contract.)
    3. K = Quantity of contracts included in the trade. (Note: each contract is 100 shares).
    4. L = Closing cost per share shown as cost of individual share.
    a. Note: If the trade starts as a Debit (contracts are Bought to Open), then profitability is determined by comparing column F to column L (Open:Close) and the close price must be MORE than open price to show a profit, otherwise it is a loss.
    b. Note: If the trade starts as a Credit (contracts are Sold to Open), then profitability is determined by comparing column F to column L (Open:Close) and the close price must be LESS then open price to show a profit, otherwise it is a loss.
    5. P = Commissions and fees paid to broker to transact the trade. This cell will always be deducted from any profitability or added to any loss.
    =IF(ISBLANK(X3),"",  If cell X3 is blank
    =IF(F3="Credit",100*(H3-U3)*(T3)-(Y3))  If cell F3 reads “Credit”
    =IF(F6="Debit",100*(U6-H6)*(T6)-(Y6))  If cell F3 reads “Debit”
    Attached Files Attached Files

  2. #2
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: If Statement with three criteria

    I used this formula in R3:

    Please Login or Register  to view this content.
    But that gives 100.04 for the first row from ($1.10 - $0.74) * 300 - $7.96 = $0.36 * 300 - $7.96 = $108.00 - $7.96 = $100.04 : not sure how you got $102.04?

    WBD
    Office 365 on Windows 11, looking for rep!

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,152

    Re: If Statement with three criteria

    Try

    =IF(ISBLANK(L3),"",IF(D3="Credit",100*(F3-L3)*(K3)-(P3),100*(U3-H3)*(T3)-(Y3)))

    and answer is 100.04 not 102.04 (330-222=108 not 110!)
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  4. #4
    Registered User
    Join Date
    10-05-2019
    Location
    Hesperia, California
    MS-Off Ver
    2016
    Posts
    3

    Re: If Statement with three criteria

    WBD,
    This formula resolves the credit part of my question and the thread after yours addresses the entire issue. Thank you for the response.

  5. #5
    Registered User
    Join Date
    10-05-2019
    Location
    Hesperia, California
    MS-Off Ver
    2016
    Posts
    3

    Re: If Statement with three criteria

    John,
    This resolves my problem. Thank you.
    Al

  6. #6
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: If Statement with three criteria

    Oops. Sorry - I didn't read the requirements very well did I? Glad it's sorted for you.

    WBD

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] IF Statement with Multi-Criteria Statement inside a Data Validation List
    By dharvey1978 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-31-2020, 09:10 PM
  2. Count statement wtih multiple criteria only showing results for one criteria
    By uhlabomber in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-17-2013, 02:47 PM
  3. [SOLVED] If statement that will look among multiple criteria and generate a statement
    By liz5818 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-01-2013, 04:12 PM
  4. IF statement with criteria
    By rwiper11 in forum Excel General
    Replies: 1
    Last Post: 01-23-2012, 08:20 PM
  5. Replies: 0
    Last Post: 03-16-2011, 01:25 PM
  6. If statement with 4 criteria
    By reggie1000 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-17-2008, 02:14 PM
  7. [SOLVED] If Statement Criteria
    By Brent E in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-11-2005, 08:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1