Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create rating with coloring cell
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create rating with coloring cell
What version of excel are you using? -- Regards, OssieMac "reza" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create rating with coloring cell
Sorry... MS Excel 2003. thanks "OssieMac" wrote: What version of excel are you using? -- Regards, OssieMac "reza" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create rating with coloring cell
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. -- Regards, OssieMac "reza" wrote: Sorry... MS Excel 2003. thanks "OssieMac" wrote: What version of excel are you using? -- Regards, OssieMac "reza" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create rating with coloring cell
I think i can use my boss notebook :D can you explain how to create this?? thanks "OssieMac" wrote: 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. -- Regards, OssieMac "reza" wrote: Sorry... MS Excel 2003. thanks "OssieMac" wrote: What version of excel are you using? -- Regards, OssieMac "reza" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create rating with coloring cell
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. -- Regards, OssieMac |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create rating with coloring cell
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 "OssieMac" wrote: 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. -- Regards, OssieMac |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create rating with coloring cell
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 "reza" wrote: 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 "OssieMac" wrote: 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. -- Regards, OssieMac |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create rating with coloring cell
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% -- Regards, OssieMac |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create rating with coloring cell
OssieMac....
Many thanks for you help... btw do you have a link about how to create S-Curve. thanks "OssieMac" wrote: 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% -- Regards, OssieMac |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create rating with coloring cell
I googled 'excel s-curve formula' and there is quite a bit out there in
cyperspace. -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell coloring | Excel Discussion (Misc queries) | |||
Compute a rating of a mtd average compared to a rating scale | Excel Discussion (Misc queries) | |||
coloring a cell if blank | Excel Worksheet Functions | |||
Cell Coloring | Excel Worksheet Functions | |||
conditional cell coloring | Excel Discussion (Misc queries) |