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


    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!