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

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?

--
Biff
Microsoft Excel MVP


"egildone" wrote in message
...
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 wrote:
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

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

????

--
Biff
Microsoft Excel MVP


"egildone" wrote in message
...
T. Valko wrote:
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