+ Reply to Thread
Results 1 to 14 of 14

Make 0 into a negative and NOT false or true

  1. #1
    Registered User
    Join Date
    04-09-2013
    Location
    Springfield, MO
    MS-Off Ver
    Excel 2010
    Posts
    5

    Make 0 into a negative and NOT false or true

    Okay, so basically I have found a way on here to make any negative numbers into 0 and I have entered the equation but for some reason it always comes out as FALSE or TRUE. I need it actually be 0 not FALSE. Please help, need the answer later today or I fail the project I'm doing!

    =SUM(B26-B27) this is the equation.
    Last edited by Slender; 04-09-2013 at 12:31 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Make 0 into a negative and NOT false or true

    =If(A1<0,0,"")
    Elegant Simplicity............. Not Always

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

    Re: Make 0 into a negative and NOT false or true

    And =SUM(B26-B27) returns FALSE ?

  4. #4
    Valued Forum Contributor
    Join Date
    11-11-2008
    Location
    Euro
    MS-Off Ver
    2007, 2010
    Posts
    470

    Re: Make 0 into a negative and NOT false or true

    it is not clear for your issue

    may be a solution that:

    Assuming cell A1 contain your data (negative or plus number)

    Cell B1- result cells:
    =IF(A1<0,0,A1)

    ???
    Best regard, -)iger-/iger
    If you are pleased with a solution mark your post SOLVED.

  5. #5
    Registered User
    Join Date
    04-09-2013
    Location
    Springfield, MO
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Make 0 into a negative and NOT false or true

    =sum(b26-b27) =if(b26<0,0,"")

    This is the equation
    Last edited by Slender; 04-09-2013 at 12:35 PM.

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

    Re: Make 0 into a negative and NOT false or true

    what values are in B26 and B27 ?

  7. #7
    Registered User
    Join Date
    04-09-2013
    Location
    Springfield, MO
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Make 0 into a negative and NOT false or true

    No when I put =sum(b26-b27) =if(b26<0,0,"") it comes back FALSE

  8. #8
    Registered User
    Join Date
    04-09-2013
    Location
    Springfield, MO
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Make 0 into a negative and NOT false or true

    b26 is $24,387.33
    b27 is $9,754.93

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

    Re: Make 0 into a negative and NOT false or true

    OK let's break it down.

    b26 is $24,387.33
    b27 is $9,754.93

    =sum(b26-b27) =if(b26<0,0,"")
    =sum($24,387.33-$9,754.93) =if($24,387.33<0,0,"")
    =sum($24,387.33-$9,754.93) =if(FALSE,0,"")
    =sum($24,387.33-$9,754.93) =""
    =sum($14,632.4) =""
    =$14,632.4="" <--this is FALSE.

  10. #10
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Make 0 into a negative and NOT false or true

    I think what you want is

    =IF((B26-B27)<0, 0, B26-B27)

    I really liked your original equation though.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Make 0 into a negative and NOT false or true

    for a start, you dont need to use sum() for a calc like that

    if you are just trying to subtract 1 from the other...

    =if(B26-B27<=0,0,B26-B27)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

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

    Re: Make 0 into a negative and NOT false or true

    Perhaps

    =MAX(0,B26-B27)

  13. #13
    Registered User
    Join Date
    04-09-2013
    Location
    Springfield, MO
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Make 0 into a negative and NOT false or true

    Quote Originally Posted by ChemistB View Post
    I think what you want is

    =IF((B26-B27)<0, 0, B26-B27)

    I really liked your original equation though.
    I do believe this worked, however instead of displaying 0 it shows this: -
    Weird.
    Hopefully it will work. Thanks!

  14. #14
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Make 0 into a negative and NOT false or true

    How is the cell formatte?
    If posting code please use code tags, see here.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1