Help with function / Formula
 

601
600
595
550
525
505
500
450
400
350
300

Using the above example in an adjacent column I would like to assign the
number 1 for all cells = to or greater than 600 , the number 2 to all cells
less than 599 but greater than 500, the number 3 to all cells less than 499
but greater than 400, and so on. I realize the if function may work however
I am using excel 2003 and I believe the limitation is approx 7. I need a
formula that will allow up to approx 50 different nemerical associations
Thanx

Help with function / Formula
 

To do 50, I would create a chart somewhere. The chart should be in
ascending numerical order, and it only needs to indicate the beginning
of each range. When you enter the beginning of the next range, that
implicitly sets the cap for the prior range. The chart lists the
beginning number of each "range" in first column, and the second column
the results for that range.

Based on what you said so far, the table would look like this:


Code:
--------------------
Column X Column Y
1 0 7
2 100 6
3 200 5
4 300 4
5 400 3
6 500 2
7 600 1
--------------------



Once your chart is complete, go back to your data set and in I1 enter
this formula to compare H1 to this chart:

=VLOOKUP(H1,$X$1:$Y$7,2,TRUE)

Of course, if your chart is bigger, adjust those chart references. Now
copy it down to get your "results".


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=56007

Help with function / Formula
 

If there is any kind of regularity in the difference of your ranges (as you
show in your example), then there may be a direct mathematical solution. If
you tell us how the ranges progress, then we could determine if such a
mathematical formula exists or not.

--
Rick (MVP - Excel)


"Jerry Bailey" wrote in message
...
601
600
595
550
525
505
500
450
400
350
300

Using the above example in an adjacent column I would like to assign the
number 1 for all cells = to or greater than 600 , the number 2 to all
cells less than 599 but greater than 500, the number 3 to all cells less
than 499 but greater than 400, and so on. I realize the if function may
work however I am using excel 2003 and I believe the limitation is approx
7. I need a formula that will allow up to approx 50 different nemerical
associations
Thanx

Help with function / Formula
 

Assuming the same relationship, this might work, but you will have to
adjust for your valid range.

=7-INT(A1/100)

Dana DeLouis


Jerry Bailey wrote:
601
600
595
550
525
505
500
450
400
350
300

Using the above example in an adjacent column I would like to assign the
number 1 for all cells = to or greater than 600 , the number 2 to all
cells less than 599 but greater than 500, the number 3 to all cells less
than 499 but greater than 400, and so on. I realize the if function may
work however I am using excel 2003 and I believe the limitation is
approx 7. I need a formula that will allow up to approx 50 different
nemerical associations
Thanx

Help with function / Formula
 

I'm pretty sure that won't handle the OP's 50 different numerical
associations<g. We need more information from the OP before a mathematical
solution can be suggested (hence, my posting elsewhere in this thread).

--
Rick (MVP - Excel)


"Dana DeLouis" wrote in message
...
Assuming the same relationship, this might work, but you will have to
adjust for your valid range.

=7-INT(A1/100)

Dana DeLouis


Jerry Bailey wrote:
601
600
595
550
525
505
500
450
400
350
300

Using the above example in an adjacent column I would like to assign the
number 1 for all cells = to or greater than 600 , the number 2 to all
cells less than 599 but greater than 500, the number 3 to all cells less
than 499 but greater than 400, and so on. I realize the if function may
work however I am using excel 2003 and I believe the limitation is approx
7. I need a formula that will allow up to approx 50 different nemerical
associations
Thanx