@ > Home > Contents > Use 1 instead of 2 interval boundaries Excel 97+

Steady (exclusive) interval ]a,b[

=SUMPRODUCT(--($A$1:$A$999>20),--($A$1:$A$999<90),$B$1:$B$999)

contains an interval for $A$1:$A$999 which therefore has to be mentioned twice.

To avoid this, shift this interval symmetrically around zero. Here we need a shift of -55, to reach a position of ]-35,35[. With the following, we can omit $A$1:$A$999 once:

=SUMPRODUCT(--(ABS($A$1:$A$999-55)<35),$B$1:$B$999)

That is: We use ABS() to treat the symmetric negative/positive side equally.

It is shorter - and faster! The gain of speed is between 10% and 30%. If you need more speed, read 5038.htm. ABS() can play this role in many places.

To use it generally (f.e. by using references instead of values) regard the following two steps:

=SUMPRODUCT(--($A$1:$A$999>LOWER),--($A$1:$A$999<UPPER),$B$1:$B$999)
=SUMPRODUCT(--(ABS($A$1:$A$999-(UPPER+LOWER)/2)<(UPPER-LOWER)/2),$B$1:$B$999)
=SUMPRODUCT(--(ABS($A$1:$A$999*2-UPPER-LOWER)<UPPER-LOWER),$B$1:$B$999)

Well - we lost the advantage "it gets shorter" here. The reason is that $A$1:$A$999 is a rather simple reference. But often, it is more complicated, and then ABS() will amazingly abbreviate it.

Inclusive interval ]a,b]:

More important for practical purposes is the following case to ensure nothing gets left out (see the red changes):

=SUMPRODUCT(--($A$1:$A$999>20),--($A$1:$A$999<=90),$B$1:$B$999)

Here the likewise transformation

=SUMPRODUCT(--(ABS($A$1:$A$999-55)<=35),$B$1:$B$999)

would be wrong! It would include 20 while it has to be excluded, because it "belongs" to a preceding interval. 

To solve the problem, subtract half of the smallest possible value (f.e 1 Cent, meaning 0,5 Cent) additionally:

=SUMPRODUCT(--(ABS($A$1:$A$999-55.005)<35),$B$1:$B$999)

The boundaries now (here 20.005 and 90.005) are "values impossible". This means, even by using the exclusive "<" we catch them all. But: You are responsible to get the values cent-rounded (here referring to the money example)!

=SUMPRODUCT(--(ABS($A$1:$A$999*2-110.01)<70),$B$1:$B$999)

is equal to the above but multiplied by 2. We need this for the last, reference based, formula:

=SUMPRODUCT(--(ABS($A$1:$A$999*2-UPPER-LOWER-smallestUnit)<UPPER-LOWER),$B$1:$B$999)

where smallestUnit = 0.01. 

Why 0.01? Why not 0.005? To abbreviate the condition, we multiplied it simply by 2.