Range formulas ( kind of )
Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Range formulas ( kind of )

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    State? Insanity! :)
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a question, I asked a similar question before, and got some great help, but as much as I hate to admit it, wasnt able to figure out how to manipulate it for my newest method of formula flow.

    I have several numbers that will equal a certain value if they are within certain range requirements.. example:

    (EDIT) they didnt line up in text, had a feeling they wouldnt .. lol
    Helms
    <200 = 2x
    201-290= 4x
    >291= 8x

    Bucklers
    <55=4x
    56-65=8x
    66-79=12x
    >80=20x

    Shields
    <100= 2x
    101-120 = 4x
    121-130= 6x
    >131=8x


    SO.. I will have AL, Shields and Bucklers in 3 different columns, and I need to make a formula that basically says " IF this value in this cell in the bucklers column is 78, then it equals 12"
    How would I do that? I have several "IF " for text searches per column that return a value for text matches. I am just unsure how to make it search for a number that is "under/over/within" a range of numbers?

    Does this make sense to anyone?

    Thanks.. I'm in a major crunch, have to get this inventory done in the next day!

    [ This Message was edited by: vampcatt on 2002-04-11 11:07 ]

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Los Angeles, CA
    Posts
    752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am not sure if this will help, but what about an =ifand function. I use this when I have to do an if with two conditions.

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    State? Insanity! :)
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm unfamiliar with the IFAND function?

    can you give me an example of how that would work with one of my ranges ?

  4. #4
    New Member
    Join Date
    Feb 2002
    Location
    State? Insanity! :)
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK.. I looked it up and gave it a shot..
    I tried this:

    =IF(C50>291,8,IF(AND(c50<290,c50>201,4,IF(c50<200,2,0))))

    but it doesn't like that formula.. can I not combine an "IF(and" with the other "if's?
    or.. anyone see my problem ?

  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    State? Insanity! :)
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    WOOOOOOOOOT
    I figured it out!
    =IF(C50>291,8,IF(AND(C50<290,C50>201),4,IF(C50<200,2,0)))
    yay!!! .. now to see if I can use it for up to 7 ranges


  6. #6
    Board Regular giacomo's Avatar
    Join Date
    Feb 2002
    Posts
    1,802
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    a different way of solving your problem might be to set up some tables on another sheet in your workbook and use VLOOKUP instead.

    For example for your Helms data you could make a table that contains the following:

    0 = 2x
    201 = 4x
    292= 8x

    and name the range "Helms" then you could use the following formula:
    =VLOOKUP(C50,helms,2)

    hope that helps.
    giacomo

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com