+ Reply to Thread
Results 1 to 5 of 5

IF statement, IFERROR or both?

  1. #1
    Registered User
    Join Date
    04-15-2020
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    2

    IF statement, IFERROR or both?

    Hi

    First time poster!

    This is probably a really basic one for most people but i'm having trouble with it. Trying to figure out a formula for the below:

    IFERROR Question.jpg

    As you can see I've currently got a very simple setup. In column D I've got the formula =C1/B1 and this works fine until i drag it down and get the divide by zero errors.

    I'm trying to incorporate an IFERROR function so i don't have to manually change every cell that has this error, however, i want 2 different outcomes. I want to show 0% if B is zero and A is Zero and i want to show -100% if B is zero and A is more than zero.

    (I know that technically there is no correct answer to dividing by zero and i don't want to get into that argument)

    Can anybody help?

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: IF statement, IFERROR or both?

    So several separate functions in one, then, try this:

    =IF(B1=0, IF(A1=0, 0%, -100%), C1/B1)
    Last edited by JBeaucaire; 04-15-2020 at 10:54 AM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.84 (24041420))
    Posts
    8,806

    Re: IF statement, IFERROR or both?

    =if(and(b1=0,a1=0),0,if(and(b1=0,a1>0),-1, c1/b1))
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Registered User
    Join Date
    04-15-2020
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    2

    Re: IF statement, IFERROR or both?

    First of all, thank you JBeaucaire

    For my understanding, am i getting this right?

    IF(B1=0, this is test number 1
    IF(A1=0, this is test number 2
    0%, this is value if true number 1
    -100%), this is value if true number 2
    C1/B1) this is value if false which applies to both tests.

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: IF statement, IFERROR or both?

    Actually, the last one: C1/B1 occurs when the first test only fails. The second test only occurs when the first test is true.

    But I think you are correctly understanding it.

+ 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. How to create a if statement with a iferror statement
    By ronald.burke in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-09-2019, 07:00 AM
  2. Iferror + if statement
    By eggselent in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-23-2019, 11:56 AM
  3. iferror statement not working
    By strollox in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-17-2018, 11:34 PM
  4. =SUM(B93:B113)/I92 with an IFERROR statement, help!
    By Niedermayer in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-28-2016, 02:24 PM
  5. Please help with and IF statement including IFERROR
    By justinr in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-28-2015, 06:21 PM
  6. [SOLVED] Please help with an IF statement including IFERROR
    By justinr in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-28-2015, 05:47 PM
  7. IFERROR Statement Help/Update...
    By kzemaitis in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-18-2014, 06:32 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