When Negatives Don’t Count: MAX( ) Function in Excel

Subscribe and get more tips for the awesome analyst. Plus, your free ebook.

The biggest operating expense of almost any business is payroll.

And unless you’re really masochistic, negative pay doesn’t exist.

The MAX( ) function is important if you’re calculating incentives or other payroll expenses.

In our above example, salespeople get a $10 incentive for every item they sell over 30 units. 35 units? $50.

But what about 25 units? We aren’t docking pay. 

Rather than hard-code these zero, use MAX( ). This will return the greatest result of a series of formulas.

In our case, we want the greater of the sales difference and 0. In effect, this puts a floor on the sales incentive of $0.

You’d be surprised how many spreadsheets around the office don’t take advantage of this function and instead hard-code over negative values.

Don’t do it! Inconsistent formulas wreak havoc.