Excel Help: How To Calculate Sales Over Quota, By Mr. Excel

Strategy: There are a couple of functions that would work in this situation.

For instance, you could use an IF function. Give the IF function a logical test and specify one calculation if the test is true and one calculation if the test is false.

If the Sales value is greater than the Quota, the IF function would return Sales (D2) – Quota (C2). If the sales did not exceed quota, then the IF function would return 0. The syntax for the IF function is =IF(logical test, value if true, value if false). Thus, as shown in Fig. 254, the formula would be: =IF(D2>C2,D2–C2,0)

Alternate Solution: Use the MAX function. One parameter to the MAX function will be D2–C2. This number will be either positive or negative, as shown in column F in Fig. 255. You can ask Excel to calculate the larger of either the calculation in F or a zero. If you think about it, the Max of 0 and a positive number is the positive number. The Max of 0 and a negative number is 0. As shown in Fig. 256, using =MAX() is slightly shorter and quicker than entering the =IF() function.

Summary: To find only positive results of a calculation, you can use either the =IF() or =MAX(0,Calc) function. Either will work. The =MAX is slightly better. Use =MIN(0,Calc) to find only negative values.

Functions Discussed: =IF(); =MAX();=MIN()

Images

References and Additional Resources

If you’re looking for more tips and tutorials, check out 91 Tips for Calculating With Microsoft Excel. This collection of easy-to-follow guides shows how to customize charts and graphs, different ways to make complex spreadsheets easier to update, and even how to play games like Craps in Excel.

Other Resources:

Microsoft Excel Official Site, https://office.microsoft.com/en-us/excel/

Bill Jelen, Microsoft Excel 2010 In Depth, Available from Amazon.com.