ranking moving up

[egildone]

I have a spread sheet that ranks 197 industry groups from 1 to 197. 4
columns rank each industry group for the current week, last week, 3
month ago and 6 months ago respectively. I want to compare the rankings
and color the cells where they are ranked red, yellow and green
depending on whether the groups are moving up in rank over time.

I am looking at using the rank function along with the IF function, but
don't quite know to go about it. Am I on the right track a or there
another function that would do the job?

Ed

 

[T. Valko]

It depends on what you mean by "whether the groups are moving up in rank
over time".

Does that mean if there's a better rank each successive period? For example:

20...17...12...5

Also, you say you want 3 different colors. What determines a "red" rank, a
"yellow" rank, a "green" rank?

Can you post several samples and let us know what results you want?

 

[egildone]

It depends on what you mean by "whether the groups are moving up in rank
over time".

Does that mean if there's a better rank each successive period? For example:

20...17...12...5

Also, you say you want 3 different colors. What determines a "red" rank, a
"yellow" rank, a "green" rank?

Can you post several samples and let us know what results you want?

Yes if there is a better rank with each successive period, then the
cells should progress from red to yellow to green. If in your example
above:

6mo ago...3mo ago...last wk...this wk
20.........17........12.......5

the 6 mo cell would be red, 3mo yellow, last wk, green, this week green

and if they are going down each period the cells should be red.

I am interested in those industry groups that are trending up in rank.

Hope this helps.

Ed

 

[T. Valko]

Ok, if *all* the ranks gets better or *all* the ranks get worse:

20...17...12....5
5...12...17...20

What about everything else in-between?

20...15...22...7
15...15...15...15
5...14...20...4
10...12...12...10
7...7...7...8

????