Microsoft Excel: How To Calculate Sales Over Quota

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.

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

See all Microsoft Excel tips

Images