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:

=SUMIF($B2:$B99,"Dollars",C2:C99)

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
.

Source:
http://www.mrexcel.com/forum/showthread.php?t=232025

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:

=SUMPRODUCT((C4:C14>20000),(D4:D14>0.5),C4:C14)*0.02

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.