Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Thread: Return Zero If Calculation Result Negative

1. I agreed to these rules
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. Member
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. I agreed to these rules
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. 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 4 users browsing this thread. (0 members and 4 guests)

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