Range formulas ( kind of )

vampcatt · Apr 11th, 2002, 12:05 PM

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 ]

Parra · Apr 11th, 2002, 12:09 PM

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.

vampcatt · Apr 11th, 2002, 12:13 PM

I'm unfamiliar with the IFAND function?

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

vampcatt · Apr 11th, 2002, 12:58 PM

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 ?

vampcatt · Apr 11th, 2002, 01:43 PM

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

giacomo · Apr 11th, 2002, 04:24 PM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 11th, 2002, 12:05 PM	#1
vampcatt New Member Join Date: Feb 2002 Location: State? Insanity! :) Posts: 26	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 ]

Apr 11th, 2002, 12:09 PM	#2
Parra Board Regular Join Date: Feb 2002 Location: Los Angeles, CA Posts: 752	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.

Apr 11th, 2002, 12:13 PM	#3
vampcatt New Member Join Date: Feb 2002 Location: State? Insanity! :) Posts: 26	I'm unfamiliar with the IFAND function? can you give me an example of how that would work with one of my ranges ?

Apr 11th, 2002, 12:58 PM	#4
vampcatt New Member Join Date: Feb 2002 Location: State? Insanity! :) Posts: 26	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 ?

Apr 11th, 2002, 01:43 PM	#5
vampcatt New Member Join Date: Feb 2002 Location: State? Insanity! :) Posts: 26	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

Apr 11th, 2002, 04:24 PM	#6
giacomo Board Regular Join Date: Feb 2002 Posts: 1,802	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