Thanks for the reply.Originally Posted by MartinShort
I do know how to calculate the percent, but was wondering if there was a way to show both the actual score, and the percent, in the same cell.
So if the metric/test were out of 20, and they scored a 10, I'd want the cell to read:
10 (50%)
Is that possible?
Thanks!
Hmmm. Yes there is. Continuing from before, in D1 type
Then hide column C1
=A1 & " (" & C1 & "%)"
Martin Short
When I use the conditional formatting for the color change, I get the drop down boxes, and choose:
"Cell Value Is"
"Less Than"
And then for the formula, I try D4/2. I also tried $D$4/2.
I'm basically trying to say if less than 50%. Neither works, it always comes back as true even if its not.
To clarify, all of row '4' contains the possible scores for each task.
Then going down the chart (rows 5 onward), you get the individual scores for each task. Like:
Column D E F G H
Row
4 20 15 20 15 20
5 12 10 8 7 17
6 8 13 14 12 7
7 16 12 13 11 9
Last edited by gibson00; 07-11-2006 at 09:07 AM.
OK, I can get the highlighting to work for a single cell, and possibly a single column, but I can't seem to highlight all the scores and insert one formula to handle them all, ASSUMING that all the scores are not out of 20, so they each need to reference their own 'out of' score....
Hi sorry for the delay - lunch proved even more important!
OK - the answer lies in use of the formula part of conditional formatting.
So your columns now look like this:
A1 = 9
B1 = 20
C1 = A1/B1
D1 = =A1&" ("&C1&"%)"
Open up the Conditional Formatting dialogue box for D1 and change Cell Value to Formula. In the Formula condition type
=IF(C1<50,TRUE,FALSE)
Now select Red as the conditional format.
Copy this formula down your entire column (including formatting) and hide column C as before.
HTH
Martin
Last edited by MartinShort; 07-11-2006 at 09:26 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks