# Formula to check if value is within range

• Hi all, I'm wondering if there's a formula that could help me simplify the following calculation. I am checking whether the result of my calculation is within a specific range (between -1 and 1). To give you an example, here's how I'm checking whether -1<(A1+B1)<1:

Code
``=IF(AND((A1+B1)>-1),(A1+B1)<1)),"OK","Check calculation for error")``

Question: is there a way to simplify the

Code
``AND((A1+B1)>-1),(A1+B1)<1)``

part? I.e., and I'm totally making this up, something along the lines of

Code
``=BETWEEN((A1+B1),-1,1)``

.

My issue is that the A1+B1 part of the calc is actually a lot more complex, and I'd like to clean my formulas as clean as possible and not repeat the summation formula multiple times.

Thanks!

p.s. Would prefer to avoid using VBA/UDFs for this.

• Re: Formula to check if value is within range

If you put 1 in A1 and 0 or blank in B1, then the False string is shown in the And formula.

This seems more correct but does not meet your goal. =IF(A1+B1=MEDIAN(A1+B1,-1,1),"OK","Check calculation for error")

A similar thing is seen when A1=0.5 and B1=0.5.

• Re: Formula to check if value is within range

Thanks for the input Kenneth - but what I am looking for is to check whether the result falls anywhere between -1 and 1. As in, if (A1+B1)=0.5, or 0.75, or 0.999, or -0.3 - this would all satisfy my criteria. I am essentially checking whether the total per two sheets in my workbook reconcile, but there's often a microscopic difference between the two due to rounding issues.

Also, just as I was posting this I've realized I should be able to play with the =ROUND() formula which will allow me to check if the rounded sum is equal to 0. So, thanks again for the help - in an unexpected way this pushed me towards the solution:)

• Re: Formula to check if value is within range

You might shorten that to IF(ABS(A1+B1)<1, ...)

• Re: Formula to check if value is within range

oh, that is actually brilliant.. totally forgot about the ABS formula. thanks a lot Mike!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!