learn excel - changing cell zero

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

• Home
• Courses
• Promotions
• Schedule
• Formats
• Our Clients

Forum home » Delegate support and help forum » » learn excel - Changing a Cell to Zero

# learn excel - Changing a Cell to Zero

Resolved · Low Priority · Version Standard

Kate has attended:

### Changing a Cell to Zero

Hi,

Is there any way of making a cell to change to the value 0, if the formulae within it solves to a negative value?

Thanks

### RE: Changing a Cell to Zero

Hi Kate

This can be solved by using the IF function as part of your formula.

1. Start by putting in your formula (e.g. B3*C3).

2. Extend your formula to include the IF function. For example:

=IF(B3*C3<0,0,B3*C3)

What this tells Excel is that if the result of the formula is less than 0, then display 0 in the cell, otherwise display the result of the formula.

Hope this helps - Amanda

### RE: Changing a Cell to Zero

Hi Kate,

Using an IF function will allow you to achieve your objective.

The IF function structure is =IF(TEST, IF TRUE, IF FALSE)

Example
A. B. C.
1. 10 20 =IF(A1*B1>0, A1*B1, "0")

2. -10 20 =IF(A1*B1>0, A1*B1, "0")

Where 1, 2 represent excel row numbers and A, B, C represent excel columns

The IF calculation in cell C1 and C2 perform a test, A1*B1>0, if that test is true then it displays the result of the calculation A1*B1, however if the test is false it displays a zero value

Hope this helps.

Training courses

Training information:

 Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions What does 'Resolved' mean? Any suggestions, questions or comments? Please post in the Improve the forum thread.

### How to select certain data in an Excel 2010 workbook

If you want to select the correct data set in a page full of data, the most accurate and efficient way of doing this is to use the ''Shift and Click'' technique.

For example: If you want to select all data in cells A2 to E10, then click on cell A2, hold down the Shift key and click on cell E10 and all the data you want to see is highlighted.

Keep holding down the Shift key and you can move from cell E10 to any other cell in the spreadsheet.

View all Excel hints and tips