Instead of giving a negative result for an operation, negative results can be automatically changed to zero using functions that force negative numbers to zero in excel. The use of MAX function which will be explained is very fast and easier to use than the IF method.
What is MAX function in Excel?
The MAX function orders Excel software not to give any result below the specified value.
How is MAX function used in Excel?
MAX function is entered in an Excel worksheet as a part of the formula in a cell along with the specified range and value.
Formula or Syntax
Parameters or arguments of SUMIF function
Excel MAX function uses the value argument:
Value = Cell operation description (e.g. =A1-B3).
Now that you have been introduced to the basics of MAX function, let us try to use to solve some problems ― since the best to teach is with examples.
- Let us assume we have a broadsheet to sum the number of new customers gained by a startup company every month by comparing a number of customers from the previous month to the new month.
Figure 1. XYZ Inc. customer stats as an example for MAX function
- To force negative numbers to zero, you will need to add some arguments to the function to execute this. Use any of the Formula or Syntax listed (scroll up to see) such as
=MAX(0,B6-B5) instead of
=B6-B5to force -163 to zero.
Figure 2. MAX function with 0 before value force -174 in Fig.1 to zero.
Figure 3. MAX function with zero after value force -163 in Fig.1 to zero.
Note on the usage of MAX function
- The zero and value argument in the MAX function must be enclosed in a bracket.
- A comma should be in-between 0 and the value in the function.