Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Return Zero If Calculation Result Negative

  1. #1
    Join Date
    9th December 2007
    Posts
    4

    Return Zero If Calculation Result Negative

    Hi I am a complete novice at excel but i am learning, i currantly have a very basic knowledge of formulars at the moment i have a sheet which adds up stock =SUM(F6-D6) (told u it was basic) what i want to do is if a negative number is the answer i want the sheet to change it to zero. Any help would be great thank you in advance.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    17th October 2007
    Posts
    22

    Re: Negative Numbers To Zero

    Hey,

    Firstly, in order to get the answer to F6-D6 you don't need to use Sum, that is =F6-D6 would give you exactly the same answer.

    What you can do is use an IF statement that sets the cell value to zero if the answer if less than 0.

    Use: =IF(F6-D6<0, 0, F6-D6)

    The if statement has three arguments, the first is the condition you want to test (in this case if F6-D6 is less than 0, the second argument is what to do if the condition is true (answer is negative) and the thrid what to do if it is false (answer is positive).

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    9th December 2007
    Posts
    4

    Re: Negative Numbers To Zero

    Thank you so much I thought it was an if but had no knowledge how to use it. Great job

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,705

    Re: Negative Numbers To Zero

    Another way


    Try this formula. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas
     
     =MAX(F6-D6,0) 


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 2
    Last Post: July 1st, 2008, 23:23
  2. Return Zero If Formula Result Is Negative
    By mckfodder in forum EXCEL HELP
    Replies: 2
    Last Post: April 30th, 2008, 18:49
  3. Return Value Based On Calculation Result
    By matrix01 in forum EXCEL HELP
    Replies: 4
    Last Post: February 2nd, 2008, 10:09
  4. Return Text Based On Calculation Result
    By n0ra in forum EXCEL HELP
    Replies: 2
    Last Post: December 18th, 2007, 16:25
  5. If Formula Result Negative Then Return Zero
    By DannyCoupe in forum EXCEL HELP
    Replies: 12
    Last Post: November 7th, 2006, 04:41

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
  •  
porno