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

Microsoft Excel: How To Calculate Sales Over Quota

written by: Mr Excel•edited by: Michele McDonough•updated: 7/6/2011

Problem: In the spreadsheet shown in Fig. 253, enter a formula to calculate the excess of sales over quota on a record-by-record basis.

  • slide 1 of 3

    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()

  • slide 2 of 3


    Fig. 253Fig. 254Fig. 255Fig. 256
  • slide 3 of 3

    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, http://office.microsoft.com/en-us/excel/

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

More To Explore