learn excel - changing cell zero

AI Training: For Busy Decision Makers & Professionals Book now

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

learn excel - Changing a Cell to Zero

resolvedResolved · Low Priority · Version Standard

Kate has attended:
Excel Advanced course

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

Thanks for your question.

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:

See also:

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.


 

Excel tip:

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


Server loaded in 0.14 secs.