Thanks:  0
Likes:  0

# Thread: Range formulas ( kind of )

1. 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. 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. I'm unfamiliar with the IFAND function?

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

4. 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. 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. 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

#### Posting Permissions

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