Greater than or less than.

  • Thread starter Thread starter apfreak
  • Start date Start date
A

apfreak

This is kind of unusaul but I do need help. I am trying to creat a
ranking system in excel. Example General, Major....etc. I have
indivuald names that I have created a points sytem. As the progress
with more points the move up rank. So I have an excel sheet that has
every members name on it. In my C colum I have their total points. In
a column I have their rank. I need help creating a formula that
enables their rank to change as I change their points. I hope I
haven't lost everyone. I am trying to make this as simple as possible.
I know that the forumla is a great than or less than that I am looking
for. I have about 10 different ranks I need it to change through as
they progress with points. Any help on this matter would e greatly
appriciated.


Thanks,

Freak
 
Try this:

Assume you have set-up
the points system for each military? rank

In Sheet2 (in cols A & B, row1 downwards)
-----------------------------------------------------

General....10
Major........9
Captain.....8
etc

In Sheet1
-----------
assume you have in col C, C2 downwards, the assigned points
for the names (as stated in your post)

put in say, D2:

=IF(ISNA(OFFSET(Sheet2!$A$1,MATCH(C2,Sheet2!B:B,0)-1,0)),"--",OFFSET(Sheet2!
$A$1,MATCH(C2,Sheet2!B:B,0)-1,0))

copy D2 down col D

col D will return the military rank corresponding to the points in col C

Where the points in col C have no match with that in Sheet2,
"--" will be returned in col D
 
Think the way your post was written
might have mis-lead Vasant to point you
incorrectly to use of RANK() in Excel Help
(I might be wrong, though <g>)

Here's how you can use Excel's help
(steps for XL97 - that's what I'm in)

Click Help > Contents and Index > Index tab

Type in the box 1: rank

and you should be able to see in box 2: RANK worksheet function

Double-click on it to bring up Excel's help for the function
 
apfreak said:
This is kind of unusaul but I do need help. I am trying to creat a
ranking system in excel. Example General, Major....etc. I have
indivuald names that I have created a points sytem. As the progress
with more points the move up rank. So I have an excel sheet that has
every members name on it. In my C colum I have their total points. In
a column I have their rank. I need help creating a formula that
enables their rank to change as I change their points. I hope I
haven't lost everyone. I am trying to make this as simple as possible.
I know that the forumla is a great than or less than that I am looking
for. I have about 10 different ranks I need it to change through as
they progress with points. Any help on this matter would e greatly
appriciated.
Click to expand...

Here is a relatively simple solution.
Create a list of minimum points for each rank. For example, Sheet2!A1:B10
might look like this:
0 lieutenant
2 captain
4 major
7 lieutenant-colonel
8 colonel
12 brigadier
15 major-general
16 lieutenant-general
18 general
25 field marshall
This list should be in ascending order, but the points can be whatever you
wish - there is no need for the increments to be equal, for example. The
first entry in the table should be for the lowest number of points there
could ever be in your column C. So, for example, if you need to get 1 point
before becoming lieutenant, include a row with whatever you want displayed
for 0 points.

Then all you need is this formula in D1 (or wherever), copied down:
=VLOOKUP(C1,Sheet2!$A$1:$B$10,2)
 
I apologize ... Max is absolutely correct. I didn't catch the part about the
"rank" being military and not numeric!
 
I like this idea...less confusing




Here is a relatively simple solution.
Create a list of minimum points for each rank. For example
Sheet2!A1:B10
might look like this:
0 lieutenant
2 captain
4 major
7 lieutenant-colonel
8 colonel
12 brigadier
15 major-general
16 lieutenant-general
18 general
25 field marshall
This list should be in ascending order, but the points can be whateve
you
wish - there is no need for the increments to be equal, for example
The
first entry in the table should be for the lowest number of point
there
could ever be in your column C. So, for example, if you need to get
point
before becoming lieutenant, include a row with whatever you wan
displayed
for 0 points.

Then all you need is this formula in D1 (or wherever), copied down:
=VLOOKUP(C1,Sheet2!$A$1:$B$10,2)



I am still however having trouble with it. What I have is in column C
For example I will be moveing the total points up by 2 or down by
with this change of total points I want the Rank to change automaticly
My points will run all the way up to 64,000 and the highest rank wil
be a general
 
I am still however having trouble with it. What I have is in column C.
For example I will be moveing the total points up by 2 or down by 2
with this change of total points I want the Rank to change automaticly.
My points will run all the way up to 64,000 and the highest rank will
be a general.
Click to expand...

Guess I'm not really sure what's the problem you're still having,
as IMO, Paul gave a good explanation for the VLOOKUP set-up

The triggers for automatic change in ranks (if any) will be according
to the limits defined by you in the VLOOKUP table.

It doesn't depend on the magnitude of
the incremental change of the points per se.

Using the example given by Paul:
0 lieutenant
2 captain
4 major
7 lieutenant-colonel
Click to expand...
etc

The trigger points for any automatic change in ranks
in the VLOOKUP (in col D, say) for any change in points in col C
for the above VLOOKUP table would be 2, 4, 7, and so on.

Do you mean that you have a rank corresponding to
every change of 2 points from zero to 64,000?

If so, that's a staggering 32,000* different ranks ??
*64,000 divided by 2
 
Back
Top