+ Reply to Thread
Results 1 to 6 of 6

If function negative numbers

  1. #1
    Registered User
    Join Date
    07-08-2014
    Location
    Portland, Oregon, USA
    MS-Off Ver
    2010
    Posts
    10

    If function negative numbers

    I have an if function statement that returns multiple possible results in column I from a formula in column H. The formula works unless the result in column H is a negative number, e.g., -4, then I get a 0 in Column I instead of the expected 5. How can I get my If statement to calculate the negative number?

    Column H
    =IF(H8="N/A",0,IF(H8="Perpetual",0,IF(H8<=1,5,IF(H8=2,3,IF(H8>=3,0,"")))))

    Column I
    =IF(H8="N/A",0,IF(H8="Unknown",5,IF(H8<=1,5,IF(H8=2,3,IF(H8>=3,0,"")))))

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: If function negative numbers

    If you're getting 0 from that formula, it means H8 is NOT a numerical value of -4, it's just a text string.

    Is H8 the result of another formula? What's that formula?

  3. #3
    Registered User
    Join Date
    07-08-2014
    Location
    Portland, Oregon, USA
    MS-Off Ver
    2010
    Posts
    10

    Re: If function negative numbers

    Many apologies, Column H formula is =IF(G8="N/A","N/A",IF(G8="Perpetual","Perpetual",IF(G8>=TODAY(),DATEDIF(TODAY(),G8,"Y"),"-"&DATEDIF(G8,TODAY(),"Y")))). I copied over the wrong formula.

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: If function negative numbers

    Yep, this is the part that is generating the negative number

    "-"&DATEDIF(G8,TODAY(),"Y")

    The & symbol is shorthand for Concatenate, which combines 2 text strings into 1 string.
    So it's result therefor is a text string, not a number.

    Try changing that to
    -DATEDIF(G8,TODAY(),"Y")

  5. #5
    Registered User
    Join Date
    07-08-2014
    Location
    Portland, Oregon, USA
    MS-Off Ver
    2010
    Posts
    10

    Re: If function negative numbers

    That works perfectly! Thank you so much!

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: If function negative numbers

    You're welcome.

+ 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] MROUND function with negative numbers
    By DPKologie in forum Excel General
    Replies: 9
    Last Post: 03-03-2019, 03:44 PM
  2. [SOLVED] Function for set of numbers being all positive or negative
    By Brennen81 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-12-2014, 08:56 AM
  3. how to use IF function with negative numbers
    By loopiloo in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 03-13-2013, 05:50 AM
  4. Replies: 4
    Last Post: 11-10-2011, 06:01 PM
  5. [SOLVED] I get some negative numbers when I use the RAND() function--Why?
    By CaliDave in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-05-2006, 08:25 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