+ Reply to Thread
Results 1 to 5 of 5

IF/OR Formula

  1. #1
    Registered User
    Join Date
    11-01-2017
    Location
    New York
    MS-Off Ver
    2016
    Posts
    2

    IF/OR Formula

    Hello - I am looking to create a basic % Price Change formula with some IF/OR logic (I think that is the logic I need).

    The criteria is if the prior price is 0 to show N/A. So my formula is as follows:

    IF(U2=0,"N/A",(G2-U2)/U2)

    Column U represents the prior day price
    Column G represents the current day price

    Now this works.

    Next piece of logic I found is for certain type of instruments they price at a negative value. This is causing my formula to return the wrong sign. My thought was to nest in an OR statement to say:

    =IF(OR(U2=0,"N/A",U2>0),(G2-U2)/U2),-(G2-U2)/U2)

    However, this is not working.

    Any idea how I can get this to pull in?

    Here are some examples.

    Security 123 - Prior price = 0 - Current price = 99.9153 = Result should be N/A
    Security 456 - Prior price = -6.4327 - Current price = -6.7507 = Result should be -4.943%
    Security 789 - Prior price = 23.28 - Current price = 22.80 = Result should be -2.062%

    Thank you!

  2. #2
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: IF/OR Formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: IF/OR Formula

    this is what I worked on so I might as well post it but it doesn't much differ from Jose's...
    =IF(U2=0,"N/A",IF(U2>0,(G2-U2)/U2,-1*(G2-U2)/U2))
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  4. #4
    Registered User
    Join Date
    11-01-2017
    Location
    New York
    MS-Off Ver
    2016
    Posts
    2

    Re: IF/OR Formula

    Thanks! This worked. Is the 2 IF statements separated with a comma act as an OR?

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: IF/OR Formula

    your statement... =IF(OR(U2=0,"N/A",U2>0),(G2-U2)/U2),-(G2-U2)/U2), this part =IF(OR(U2=0,"N/A",U2>0) was creating a condition that Excel couldn't reconcile.
    in an OR statement it is supposed to be if this condition OR this condition is present then do this, you were putting in the complete condition into the OR statement.
    you were trying to say that if U2=0, put N/A or if U2>0 ... then what? So it needed to be if U2=0, N/A, if U2>0 then take G2 and subtract U2 and divide that result by U2 unless U2 is less than 0, then take the negative of G2 minus U2 divided by U2. So in effect it is like an OR statement, you are saying if this condition exists do this, the second if is if this condition exists then do that, and you can add more ifs if needed.

    But in certain conditions the or statement works better up front, like if this OR this OR this exists, do that, otherwise do something else. Hope that makes sense.

+ 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. Replies: 8
    Last Post: 09-22-2017, 05:41 AM
  2. Excel formula bar to display the result of the formula , not the formula?
    By max_max in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-18-2016, 07:15 PM
  3. Replies: 11
    Last Post: 06-06-2014, 03:34 PM
  4. how to hide formula in formula box, view lookup result in formula box?
    By vengatvj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-14-2013, 04:06 PM

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