+ Reply to Thread
Results 1 to 5 of 5

A range inside a nested IF

  1. #1
    Registered User
    Join Date
    02-26-2009
    Location
    BC, Canada
    MS-Off Ver
    Excel 2007
    Posts
    7

    A range inside a nested IF

    Hey, I'm looking to insert a range of numbers inside a nested IF statement but it's not working so well. Here's an example:

    =IF(A1=1:3, B1=-5, IF(A1=4:6, B1=-4, IF(A1=7:9, B1=-3)))

    I think the problem is the 1:3, 4:6 and 7:9. I don't want to have to create If 1 Then -5, If 2 Then -5, If 3 Then -5, If 4 Then -4, etc. Anyone know of a better way?

  2. #2
    Registered User
    Join Date
    01-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    67

    Re: A range inside a nested IF

    use this in B1:
    =INT((A1-1)/3)-5

  3. #3
    Registered User
    Join Date
    02-26-2009
    Location
    BC, Canada
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: A range inside a nested IF

    Hey thanks for the quick reply. The system I used there was a quick example. The actual one is:

    1 = -5
    2-3 = -4
    4-5 = -3
    6-7 = -2
    etc

  4. #4
    Registered User
    Join Date
    01-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    67

    Re: A range inside a nested IF

    how about:
    =INT(A1/2)-5

  5. #5
    Registered User
    Join Date
    02-26-2009
    Location
    BC, Canada
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: A range inside a nested IF

    Man I don't know how you figure that out so easily but thank you!

    edit: actually now that I think about it the answer is so obvious. Still, it would've taken me much longer to get there. Thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1