This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Calculate Sales Over Quota
Problem: I need to enter a formula to calculate the excess of sales over quota on a record-by-record basis. How do I do it?
Strategy: Most people would use an IF to solve this. Surprisingly, there is a faster way by using MAX.
Figure 407 Most people would use an IF.
Instead of using IF, you can use the MAX function. One parameter to the MAX function will be D2-C2. This number will be either positive or negative. The second argument will be zero. The MAX will be comparing zero and the calculation result in each row. If the calculation is positive, it will win. If the calculation is negative, then the zero will win and MAX will show zero instead of the negative values.
Figure 408 MAX of 0 and a calculation returns only positive results.
If your coworkers hassle you for using such a bizarre formula, tell them that MAX is slightly faster to calculate than IF.
If you want to see only negative rows, use =MIN(D2-C2,0).