I am looking to create a formula that will give a certain range of numbers a
specific value. For example:

If the number falls between 248 and 258 then give it a value of 4.5
If the number falls between 241 and 247 then give it a value of 4
If the number falls between 230 and 240 then give it a value of 3.5
If the number falls between 220 and 229 then give it a value of 3
If the number falls between 209 and 219 then give it a value of 2.5
If the number falls between 198 and 208 then give it a value of 2

and so forth.

Any assistance with this problem would be greatly appreciated

Try something like this:

Put this table of values in A1:C6
198 208 2.0
209 219 2.5
220 229 3.0
230 240 3.5
241 247 4.0
248 258 4.5

Next....
put this formula in F1
=VLOOKUP(E1,$A$1:$C$6,3,1)
(it will initially display #N/A)

Last...enter a number in E1...The formula in F1 will assign it a value.

Example:
E1: 215
F1: displays 2.5

Is that something you can work with?
Post back with any questions.
***********
Regards,
Ron

XL2002, WinXP


"urrbie" wrote:

I am looking to create a formula that will give a certain range of numbers a
specific value. For example:

If the number falls between 248 and 258 then give it a value of 4.5
If the number falls between 241 and 247 then give it a value of 4
If the number falls between 230 and 240 then give it a value of 3.5
If the number falls between 220 and 229 then give it a value of 3
If the number falls between 209 and 219 then give it a value of 2.5
If the number falls between 198 and 208 then give it a value of 2

and so forth.

Any assistance with this problem would be greatly appreciated

This can be easily done using VLOOKUP to a table containing two
columns: the minimum of the banding and the band. Most times VLOOKUP is
used with the FALSE range_lookup for an exact match. This is the one
rare use where TRUE range_lookup can be used. In this case if an exact
match is not found, the next largest value that is less than
lookup_value is returned.

Set up a lookup table like this in two columns...

1
1.5
198 2
209 2.5
220 3
230 3.5
241 4
248 4.5


You'll have to fill in the min values for bands 1 and 1.5.

And write the lookup as VLOOKUP(lookup_value,table_array,2,TRUE)

Regards,

Leslie Bantleman


urrbie wrote:
I am looking to create a formula that will give a certain range of numbers a
specific value. For example:

If the number falls between 248 and 258 then give it a value of 4.5
If the number falls between 241 and 247 then give it a value of 4
If the number falls between 230 and 240 then give it a value of 3.5
If the number falls between 220 and 229 then give it a value of 3
If the number falls between 209 and 219 then give it a value of 2.5
If the number falls between 198 and 208 then give it a value of 2

and so forth.

Any assistance with this problem would be greatly appreciated

If you don't want to use a table in your sheet, then you can use the
following formula which is similar to what you have already been given. The
only difference is the table has been "imbeded" in the formula itself.

=VLOOKUP(A1,{198,2;209,2.5;220,3;230,3.5;241,4;248 ,4.5},2)

The best solution would be to come up with a mathematical formula, but we
would need to know where your numbers are coming from. They seem random to
me.

"urrbie" wrote:

I am looking to create a formula that will give a certain range of numbers a
specific value. For example:

If the number falls between 248 and 258 then give it a value of 4.5
If the number falls between 241 and 247 then give it a value of 4
If the number falls between 230 and 240 then give it a value of 3.5
If the number falls between 220 and 229 then give it a value of 3
If the number falls between 209 and 219 then give it a value of 2.5
If the number falls between 198 and 208 then give it a value of 2

and so forth.

Any assistance with this problem would be greatly appreciated

This can be easily done using VLOOKUP to a table containing two
columns: the minimum of the banding and the band. Most times VLOOKUP is
used with the FALSE range_lookup for an exact match. This is the one
rare use where TRUE range_lookup can be used. In this case if an exact
match is not found, the next largest value that is less than
lookup_value is returned.

Set up a lookup table like this...

1
1.5
198 2
209 2.5
220 3
230 3.5
241 4
248 4.5


You'll have to fill in the min values for bands 1 and 1.5.

And write the lookup as
VLOOKUP(lookup_value,table_array,col_index_num,TRU E)

Regards,

Les Bantleman



urrbie wrote:
I am looking to create a formula that will give a certain range of numbers a
specific value. For example:

If the number falls between 248 and 258 then give it a value of 4.5
If the number falls between 241 and 247 then give it a value of 4
If the number falls between 230 and 240 then give it a value of 3.5
If the number falls between 220 and 229 then give it a value of 3
If the number falls between 209 and 219 then give it a value of 2.5
If the number falls between 198 and 208 then give it a value of 2

and so forth.

Any assistance with this problem would be greatly appreciated

This can be easily done using VLOOKUP to a table containing two
columns: the minimum of the banding and the band. Most times VLOOKUP is
used with the FALSE range_lookup for an exact match. This is the one
rare use where TRUE range_lookup can be used. In this case if an exact
match is not found, the next largest value that is less than
lookup_value is returned.

Set up a lookup table like this...

1
1.5
198 2
209 2.5
220 3
230 3.5
241 4
248 4.5


You'll have to fill in the min values for bands 1 and 1.5.

And write the lookup as VLOOKUP(lookup_value,table_array,2,TRUE)

Regards,

Leslie Bantleman


urrbie wrote:
I am looking to create a formula that will give a certain range of numbers a
specific value. For example:

If the number falls between 248 and 258 then give it a value of 4.5
If the number falls between 241 and 247 then give it a value of 4
If the number falls between 230 and 240 then give it a value of 3.5
If the number falls between 220 and 229 then give it a value of 3
If the number falls between 209 and 219 then give it a value of 2.5
If the number falls between 198 and 208 then give it a value of 2

and so forth.

Any assistance with this problem would be greatly appreciated