+ Reply to Thread
Results 1 to 9 of 9

Multiple IF THEN statements in one cell

  1. 02-13-2013, 02:27 AM #1
    faist001
    faist001 is offline
    Registered User
    Join Date
    02-13-2013
    Location
    san diego, ca
    MS-Off Ver
    Excel 2001 for Mac
    Posts
    8

    Multiple IF THEN statements in one cell

    Hello Everyone! I am new to the forum hoping you can help me with an issue that has been pestering me all day today.

    I have 3 columns and 1,000's of rows each cell contains a value of 0 to 500

    Column B = value b
    Column C = value c
    Column D = % change between x and y

    I need to make a few rules for Column D as to not end of with a #DIV/0! error but I'm not sure how to write the formula. I need to tell Excel to tweak value b or value c if either equals 0 in order to not run into the dreaded #DIV/0!. So far I have a great IF statement that covers column b but I cant figure out the IF statement for column c

    Rule 1 is an IF statement in regards to the value of b and looks like this =IF(B3>0,(C3-B3)/B3,(C3-(C3-0.1)/(C3*99))). This works perfectly and I avoid the #DIV/O! error
    Rule 2 needs to be the same statement, Anyway suggestions?
    Last edited by faist001; 02-13-2013 at 02:29 AM.
    Reply With QuoteRegister To Reply
  2. 02-13-2013, 02:44 AM #2
    Excel4Everything
    Excel4Everything is offline
    Registered User
    Join Date
    01-25-2013
    Location
    Finland
    MS-Off Ver
    Excel 2011
    Posts
    18

    Re: Multiple IF THEN statements in one cell

    How about:
    Please Login or Register  to view this content.
    Cheers!
    Reply With QuoteRegister To Reply
  3. 02-13-2013, 01:56 PM #3
    faist001
    faist001 is offline
    Registered User
    Join Date
    02-13-2013
    Location
    san diego, ca
    MS-Off Ver
    Excel 2001 for Mac
    Posts
    8

    Re: Multiple IF THEN statements in one cell

    Thanks so much for the reply. This statement gets me the results that I was looking for. Thanks
    Reply With QuoteRegister To Reply
  4. 02-13-2013, 02:02 PM #4
    faist001
    faist001 is offline
    Registered User
    Join Date
    02-13-2013
    Location
    san diego, ca
    MS-Off Ver
    Excel 2001 for Mac
    Posts
    8

    Re: Multiple IF THEN statements in one cell

    Now I have run into this error. If both value b and value c are 0, the output in column D reads "C3 <= 0" How do I tell excel that if both value b and value c are 0 then output for column d (% change) should be 0 as well
    Reply With QuoteRegister To Reply
  5. 02-13-2013, 02:07 PM #5
    dluhut
    dluhut is offline
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: Multiple IF THEN statements in one cell

    Hello faist001, I'm a visualize person, thus forgive me if I'm wrong. Try the code below.

    Please Login or Register  to view this content.
    Last edited by dluhut; 02-13-2013 at 02:13 PM.
    Reply With QuoteRegister To Reply
  6. 02-13-2013, 02:14 PM #6
    faist001
    faist001 is offline
    Registered User
    Join Date
    02-13-2013
    Location
    san diego, ca
    MS-Off Ver
    Excel 2001 for Mac
    Posts
    8

    Re: Multiple IF THEN statements in one cell

    Attached is one group of data from my spreadsheet - hopefully this helps. How do i reward those that have helped? Glad to do that.

    Attachment 214034
    Reply With QuoteRegister To Reply
  7. 02-13-2013, 02:18 PM #7
    dluhut
    dluhut is offline
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: Multiple IF THEN statements in one cell

    Hi Faist001,

    Tried to open the attachment, but give me an error "Invalid Attachment specified"

    These are the steps in giving people. At the bottom of each of the post of the user, there's a star like icon (below join date, location, ms-off ver and posts) click on it and you'll be adding reputation to those who have helped you. I'm saying this not because i want the reputation. I'm far from being an expert...thus you may want to give it to others whom have really helped in your work =)
    Reply With QuoteRegister To Reply
  8. 02-13-2013, 02:20 PM #8
    dluhut
    dluhut is offline
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: Multiple IF THEN statements in one cell

    By the way, I made a mistake on the code. You mention that you want it to be both 0, so instead of OR(B3=0, C3=0), change it to AND(B3=0, C3=0)
    Reply With QuoteRegister To Reply
  9. 02-13-2013, 03:06 PM #9
    faist001
    faist001 is offline
    Registered User
    Join Date
    02-13-2013
    Location
    san diego, ca
    MS-Off Ver
    Excel 2001 for Mac
    Posts
    8

    Re: Multiple IF THEN statements in one cell

    many many thanks. this new correction is exactly what i needed. Thanks!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1