Create rating with coloring cell

[reza]

HI Guys...

I have data which everyday will change.
from that data i want to create rating for each items. for example
A = 0.13%
B = 1%
C = 2.3%
D = 1.2%
E = 0%
F = 2.1% , so on

Based on that data, i want 3 of highest and lowest percentage will have
different color. So C = Blue , F =Yellow, D = Green and the lowest E = Red.
Since the percentage everyday will get updated so they will change it.
How to archive that....

need to hear your explanation. and thank in advance

reza

 

[OssieMac]

What version of excel are you using?

 

[reza]

Sorry... MS Excel 2003.

thanks

What version of excel are you using?

 

[OssieMac]

Then I think that the only way to do this is with macro code. I haven't tried
but think I can handle it but first do you want that sort of answer? My
apologies because I should have said this in my previous post.

Excel 2007 handles it easily with conditional format but not earlier versions.

 

[reza]

I think i can use my boss notebook
can you explain how to create this??

thanks

 

[OssieMac]

Helo Reza,

I take it you mean how to create the conditional format in xl2007 so here it
is.

Firstly the easiest way to apply Conditional Format is to select the entire
range and apply the formula as if it applies to the first cell only. The
following examples look like they apply to cell A2 only but Excel actually
applies it to all the cells in the selection.

Ensure that Home ribbon is selected.
Select the full range of percentages involved.
Select Conditional Format (See Styles block just past half way across ribbon)
Select Manage Rules.
Click New Rule.
Select ‘Use a formula to determine which cells to format.’

In the field below ‘Format cells where this formula is true’ enter the
following formula where A2 is the first cell in the selection and $A$2:$A$30
is the entire range to which the Conditional format is to be applied. (You
can select the range on the worksheet to insert it as you can do in the
formula bar on a worksheet. Also note the absolute addressing of the range
but not for cell A2.)

=A2=LARGE($A$2:$A$30,1)

Click Format button.
Click the Font or Fill tab at the top depending on whether you want to
change the font or background colour.
Select Yellow.
Click OK and OK again.
Click Apply.
That has added the Conditional Format for the highest value.

Click New Rule.
Repeat the above but this time the formula is for the second highest value
as follows. (Note the parameter 2 in lieu of 1 in the previous formula).

=A2=LARGE($A$2:$A$29,2)
Set Format to Green colour.

Repeat the above again with the formula.
=A2=LARGE($A$2:$A$29,3)
Set format to Blue colour.

Repeat the above again with the formula. (Note parameter 1 is the smallest
value)
=A2=SMALL($A$2:$A$29,1)
Set format to Red colour.

Ensure you click Apply before closing the Conditional Format dialog box.

Hope I have explained it all properly but feel free to get back to me if you
have problems.

 

[reza]

OssieMac....

Hehehehe...sorry, i still don't get in.
for Cell A2...its it Name or Percentage colom.
Example.
Colom A (Name) Colom B (Percentage)
A 0.1%
B 1%
C 1.1%
D 0.3%
etc

thanks

 

[reza]

OssieMac...

Horeee....i can do that...
thanks for you...but now i have another problem.
i have values like this
=$E$14:$E$16,$E$18:$E$21,$E$23:$E$24
but when click OK, i have error message "You May not use unions,
intersections, or array constants for Conditional Formating Criteria"...

can you teach me how to solve this??

thanks again

 

[OssieMac]

It is a bit tedious but you can get around it. You need to create a
contiguous column of the data in an out of the way place on the worksheet. In
my example I used column Z.

In the example data below I have used column A to number the rows.
Column B has the percentages and is the column where the Conditional Format
is applied. Note that the cells with percentages are non contiguous and for
the example I have just left the intermediate cells blank.

In column Z link the cells to percentages in column B with formulas as
follows.
In Z2 insert =B2
In Z3 insert =B3
And etc down to Z7 insert = B7
Then
Z8 insert =B11
Z9 insert =B12
And etc so that Z2:Z21 contains a contiguous set of data representing the
non contiguous ranges B2:B7, B11:B17, B21:B23, B27:B30

Now for the conditional format.
Select cell B2 only. (Note only one cell this time)
Insert the Conditional Format formulas and Formats for the 4 conditions as
per my previous instructions except this time the formulas for the functions
LARGE and SMALL uses the range in column Z as follows:

=B2=LARGE($Z$2:$Z$21,1)

=B2=LARGE($Z$2:$Z$21,2)

=B2=LARGE($Z$2:$Z$21,3)

=B2=SMALL($Z$2:$Z$21,1)

Ensure you click Apply before exiting the Conditional Format dialog box.

Select cell B2 and Copy.

Select all of the cells for the conditional format. (Select the first group
and then hold the Ctrl key down while you select the rest of the groups.)

Then Paste Special Formats. (Ensure it is Formats).

Example Data:
ColA ColB Col Z
2 0.13% 0.13%
3 1.00% 1.00%
4 2.30% 2.30%
5 1.20% 1.20%
6 0.00% 0.00%
7 2.10% 2.10%
8 2.80%
9 2.40%
10 0.70%
11 2.80% 0.80%
12 2.40% 2.00%
13 0.70% 0.90%
14 0.80% 2.10%
15 2.00% 1.30%
16 0.90% 2.90%
17 2.10% 0.20%
18 1.00%
19 1.20%
20 2.70%
21 1.30% 2.80%
22 2.90%
23 0.20%
24
25
26
27 1.00%
28 1.20%
29 2.70%
30 2.80%

 

[reza]

OssieMac....

Many thanks for you help...
btw do you have a link about how to create S-Curve.

thanks

 

[OssieMac]

I googled 'excel s-curve formula' and there is quite a bit out there in
cyperspace.