1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Replacing a formula value if it is negative with another cell value

Discussion in 'Ask an Excel Question' started by Matt_Straya, May 16, 2018.

  1. Matt_Straya

    Matt_Straya New Member

    Messages:
    28
    Hi,
    I have this formula
    Code (vb):
    =IFERROR((C3-D3)/B3,0)*E3
    that may result in a negative number ($). If it does I want to display the value in B3.

    Grateful for any ideas

    Attached Files:

  2. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    751
    Quick and dirty, perhaps this one
    =IF(IFERROR((C3-D3)/B3,0)*E3<0,B3, IFERROR((C3-D3)/B3,0)*E3)

    But I don't like it very much. I'm sure there is a more elegant one possible. Can't look at your data for the moment to evaluate it.
  3. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    751
    Not a lot of data in your file.
    =IF((C3-D3)/B3*E3<0,B3,IFERROR((C3-D3)/B3*E3,0)
    Thomas Kuriakose likes this.
  4. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,851
    Or,

    =IF(D3>C3,B3,IFERROR((C3-D3)/B3,0)*E3)

    Regards
    Bosco
    Matt_Straya and Thomas Kuriakose like this.
  5. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    751
    or?
    =IFERROR(IF(D3>C3,B3,(C3-D3)/B3*E3),0)
    Matt_Straya likes this.
  6. Matt_Straya

    Matt_Straya New Member

    Messages:
    28
    Thanks guys! brilliant! Both seem to work

Share This Page