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

Steady (exclusive) interval ]a,b[


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:


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:


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


Here the likewise transformation


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:


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


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


where smallestUnit = 0.01. 

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