color coding by order of value (highest to lowest)

S

Stephan Smith

Hi Folks,

I was wondering if someone might know a macro to do the following:

I keep a database of the number of incoming calls my call center takes. I
have a worksheet for each day of the week. Rows are broken up into 30 min.
increments and columns represent days. I'd like to color each colum based on
value; ie:

Mar16 Mar23 Mar30
11:00 20 18 15
11:30 24 20 18
12:00 24 18 22
12:30 25 22 23
13:00 26 23 25
13:30 30 26 28
14:00 30 26 28
14:30 25 28 24
15:00 23 15 23
15:30 20 15 21



*I know, my color scheme sucks, but it's a rush job ;)*. More or less I
want it to work like the above, with the highest going from one spectrum,
down to the lowest into another spectrum so I can see at a glance, without
making a chart(because I have that too, but for other purposes). Basically
what I would like to see is the trend of peaks over the last couple of
years.

So I guess, the question is it possible?

Thanx in Advance,

Steph S.
 
B

Bob Phillips

You could use conditional formatting for the 3 largest/smallest values in a
column (better IMO that colouring them all, too much colour obscures
information). The formulae would be

=LARGE(($A$1:$A$20,1) or,2 or ,3

or similarly SMALL for the smallest.

Change the condition in CF from Condition 1 to Formula Is.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
S

Stephan Smith

Thanx for the Suggestion, however when I apply the CF as Formula Is
=LARGE(($B$3:$B$50),1) and set the format color, it turns the whole column
to the same color, not the just the highest value(this is if I select the
whole column. If I select a single cell, it turns that particular cell the
color and nothing else....). However, if I go to an empty cell and type in
the formula, it gives me the largest value in the column(not to suprising
there..)

I've tried SMALL as well and it didn't do anything at all. And lastly, if I
try two LARGE(,1 and ,2), it only takes into consideration the first and
colors the entire column...
 
B

Bob Phillips

Sorry Stephan, a bit lacking.

Select the range to apply it to, and assuming that say B3 is the activecell
then your formula should be

=B3=LARGE(($B$3:$B$50),1)

etc.

as you just want the formula to return True or False to determine whether
the formatting is tom be applied ot not.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
S

Stephan Smith

Thanx guys, that was what I needed(minus a couple of $'s so I could copy and
paste the format over ;)) I also had a friend give me another, being "cell
value is" "equal to" =MAX(B$3:B$50), however it was only good if I wanted
just the max, and not the two or three below it..

Thanx again.

Steph.
 
Top