Range formulas ( kind of )

vampcatt

New Member
Joined
Feb 22, 2002
Messages
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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.
 
Upvote 0
I'm unfamiliar with the IFAND function?

can you give me an example of how that would work with one of my ranges ?
 
Upvote 0
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 ?
 
Upvote 0
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 :)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top