Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2009
    Posts
    2

    Exclamation Unanswered: if formula to check if the number is in a certain range

    Here is the raw data:

    Range---Cat3---Cat2
    0 -5------2------5
    6-11------3------6
    12-25-----5-----10
    26-50-----8-----15
    51-100---12-----20
    100+-----15-----30

    Ex:1
    there will be an input box A2. when the user enters the number the formula will check if its in the correct range and set a high and a low range for that number for each catagory (Cat3,Cat2) Example user enters an 8 in A1.

    There are two catagories and a high and a low range.

    A3 range for catagory 3 (B3) low= 5 (8-3) (C3) high= 11 (8+3)
    A4 range for catagory 2 (B4) low = 2 (8-6) (C4) high = 14 (8+6)

    Ex:2
    If user enters 80 in A2
    B3 = 68
    C3= 92
    B4 = 60
    C4 = 100

    I have tried several formula's and I can't get anything to work right.
    Last edited by wolgie; 09-08-09 at 15:47.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You need to divide your range column into two columns - one with the lower bound, and one one with the upper. The upper column will just be for reference. Also, replace "100+" with "101" in the first column. Then you'll be able to use the table in VLOOKUP formulae:
    For B3 - "=A2 - VLOOKUP(A2, [Range], 3, True)"
    For C3 - "=A2 + VLOOKUP(A2, [Range], 3, True)"
    For B3 - "=A2 - VLOOKUP(A2, [Range], 4, True)"
    For B4 - "=A2 + VLOOKUP(A2, [Range], 4, True)"
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •