Microsoft Office Tutorials and References
In Depth Information
WHY THE MINUS MINUS? COERCE NUMBERS FROM TRUE/ FALSE
Alternate Strategy: While all the solutions presented so far are going to amaze
your co-workers, they are all inherently dangerous. If someone inserts a new
row in the worksheet, the MOD functions won’t work as you want them to.
It was not stated in the original problem, but if the worksheet really has a
column B that identiﬁ es Dollars and GP% , then it would be safer to use a
SUMIF function to sum the dollar amounts:
This formula instructs Excel to look through B2:B99. If the value in that row
says "Dollars" , Excel adds up the corresponding value from column C. With
this solution, there is no worry that dollars on even rows will accidentally shift
to odd rows.
Summary: While you can guru-out with SUMPRODUCT solutions galore, the
simplest solution might be to use SUMIF .
WHY THE MINUS MINUS?
COERCE NUMBERS FROM TRUE/FALSE
Challenge: While IF and other functions that expect logical tests can easily
convert TRUE and FALSE values to 1s and 0s , the SUMPRODUCT function
cannot do this. Why do you sometimes use a minus minus in SUMPRODUCT ?
In Figure 9, for example, the SUMPRODUCT formula to calculate a 2% bonus for
sales above $20,000 and with GP% above 50% fails:
If you simply build a SUMPRODUCT formula with your criteria and the numeric
ﬁ eld, you end up with calculations such as TRUE * TRUE * 21000 , which
SUMPRODUCT incorrectly evaluates to 0 .
Figure 9. You would think Excel’s Boolean
logic rules could handle this.